小编典典

通过自我加入进行更新

sql

我想更新一个表以指示某些行是其他行的父表,因此我在表中添加了“父代”列。以下查询查找所有父母:

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”行的结尾。


阅读 149

收藏
2021-03-10

共1个答案

小编典典

Oracle不支持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
2021-03-10