使用自加入更新 [英] Update with self-join

问题描述

我想更新一个表以表明某些行是其他行的父行，因此我在表中添加了一个parentid"列.以下查询查找所有父母:

I want to update a table to indicate that some rows are parents of others, so I added a "parentid" column to the table. The following query finds all the parents:

SELECT ca1.id, ca2.id 
FROM contactassociations ca1
JOIN contactassociations ca2 ON (ca1.contactid = ca2.contactid)
where ca1.entitytable = 'EMPLOYER' AND
ca2.entitytable = 'CLIENT';

但是当我尝试调整该语法来进行更新时，它不起作用:

but when I try to adapt that syntax to do the update, it doesn't work:

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"行的结尾.

Note that line 7 column 28 is the end of the "SET" line.

推荐答案

Oracle 不支持 UPDATE 语句中的 JOIN 子句.

Oracle does not support JOIN clause in UPDATE statements.

使用这个:

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

这篇关于使用自加入更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！