我想更新一个表以指示某些行是其他行的父表,因此我在表中添加了“父代”列。以下查询查找所有父母:
SELECT ca1.id, ca2.id FROM contactassociations ca1 JOIN contactassociations ca2 ON (ca1.contactid = ca2.contactid) where ca1.entitytable = 'EMPLOYER' AND ca2.entitytable = 'CLIENT';
但是当我尝试修改该语法以进行更新时,它不起作用:
UPDATE contactassociations ca1 SET ca1.parentid = ca2.id JOIN contactassociations ca2 ON (ca1.contactid = ca2.contactid) WHERE ca1.entitytable = 'EMPLOYER' AND ca2.entitytable = 'CLIENT';
我得到:
Error starting at line 6 in command: UPDATE contactassociations ca1 SET ca1.parentid = ca2.id JOIN contactassociations ca2 ON (ca1.contactid = ca2.contactid) WHERE ca1.entitytable = 'EMPLOYER' AND ca2.entitytable = 'CLIENT' Error at Command Line:7 Column:28 Error report: SQL Error: ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause: *Action:
请注意,第7行第28列是“ SET”行的结尾。
Oracle不支持JOIN语句中的子句UPDATE。
JOIN
UPDATE
用这个:
MERGE INTO contactassociations ca1 USING contactassociations ca2 ON ( ca1.contactid = ca2.contactid AND ca1.entitytable = 'EMPLOYER' AND ca2.entitytable = 'CLIENT' ) WHEN MATCHED THEN UPDATE SET parentid = ca2.id