SQL 使用自加入更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2380353/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update with self-join
提问by Paul Tomblin
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:
我想更新一个表以指示某些行是其他行的父行，因此我在表中添加了一个“parentid”列。以下查询查找所有父项：
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';
I get:
我得到：
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:
Note that line 7 column 28 is the end of the "SET" line.
请注意，第 7 行第 28 列是“SET”行的结尾。
回答by Quassnoi
Oracle does not support JOINclause in UPDATEstatements.
Oracle 不支持JOIN语句中的子句UPDATE。
Use this:
用这个：
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
回答by David Miller
I find the following style simpler to read, but you need to use an alias after the UPDATE key word, not the table name:
我发现以下样式更易于阅读，但您需要在 UPDATE 关键字后使用别名，而不是表名：
UPDATE ca1
SET ca1.parentid = ca2.id
FROM contactassociations ca1
LEFT JOIN contactassociations ca2 ON (ca1.contactid = ca2.contactid)
WHERE ca1.entitytable = 'EMPLOYER' AND ca2.entitytable = 'CLIENT'
回答by siripuram
-- Method #1
update emp set MANAGERNAME= mgr.EMPNAME
FROM SelfJoinTable emp , SelfJoinTable mgr where emp.MANAGERID = mgr.EMPID
-- Method #2
update emp
set MANAGERNAME= mgr.EMPNAME
FROM SelfJoinTable emp
LEFT OUTER JOIN SelfJoinTable mgr
ON emp.MANAGERID = mgr.EMPID
-- Method #3
update emp
set MANAGERNAME= mgr.EMPNAME
FROM SelfJoinTable emp
JOIN SelfJoinTable mgr
ON emp.MANAGERID = mgr.EMPID
-- Method #4
update emp
set MANAGERNAME= mgr.EMPNAME
FROM SelfJoinTable emp
inner JOIN SelfJoinTable mgr
ON emp.MANAGERID = mgr.EMPID
