SQL 在 Oracle 上使用内部联接更新语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2446764/
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 statement with inner join on Oracle
提问by user169743
I have a query which works fine in MySQL, but when I run it on Oracle I get the following error:
我有一个在 MySQL 中运行良好的查询，但是当我在 Oracle 上运行它时，出现以下错误：
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
SQL 错误：ORA-00933：SQL 命令未正确结束
00933. 00000 -“SQL 命令未正确结束”
The query is:
查询是：
UPDATE table1
INNER JOIN table2 ON table1.value = table2.DESC
SET table1.value = table2.CODE
WHERE table1.UPDATETYPE='blah';
回答by Tony Andrews
That syntax isn't valid in Oracle. You can do this:
该语法在 Oracle 中无效。你可以这样做：
UPDATE table1 SET table1.value = (SELECT table2.CODE
FROM table2
WHERE table1.value = table2.DESC)
WHERE table1.UPDATETYPE='blah'
AND EXISTS (SELECT table2.CODE
FROM table2
WHERE table1.value = table2.DESC);
Or you mightbe able to do this:
或者你可以这样做：
UPDATE
(SELECT table1.value as OLD, table2.CODE as NEW
FROM table1
INNER JOIN table2
ON table1.value = table2.DESC
WHERE table1.UPDATETYPE='blah'
) t
SET t.OLD = t.NEW
It depends if the inline view is considered updateable by Oracle ( To be updatable for the second statement depends on some rules listed here).
这取决于 Oracle 是否认为内联视图可更新（第二个语句的可更新取决于此处列出的一些规则 ）。
回答by Quassnoi
Use this:
用这个：
MERGE
INTO table1 trg
USING (
SELECT t1.rowid AS rid, t2.code
FROM table1 t1
JOIN table2 t2
ON table1.value = table2.DESC
WHERE table1.UPDATETYPE='blah'
) src
ON (trg.rowid = src.rid)
WHEN MATCHED THEN UPDATE
SET trg.value = code;
回答by Roland
MERGEwith WHEREclause:
MERGE带WHERE条款：
MERGE into table1
USING table2
ON (table1.id = table2.id)
WHEN MATCHED THEN UPDATE SET table1.startdate = table2.start_date
WHERE table1.startdate > table2.start_date;
You need the WHEREclause because columns referenced in the ONclause cannot be updated.
您需要该WHERE子句，因为该子句中引用的列ON无法更新。
回答by Morten Anderson
UPDATE ( SELECT t1.value, t2.CODE
FROM table1 t1
INNER JOIN table2 t2 ON t1.Value = t2.DESC
WHERE t1.UPDATETYPE='blah')
SET t1.Value= t2.CODE
回答by duvo
Do not use some of the answers above.
不要使用上面的一些答案。
Some suggest the use of nested SELECT, don't do that, it is excruciatingly slow. If you have lots of records to update, use join, so something like:
有人建议使用嵌套的 SELECT，不要这样做，它非常慢。如果您有很多记录要更新，请使用 join，例如：
update (select bonus
from employee_bonus b
inner join employees e on b.employee_id = e.employee_id
where e.bonus_eligible = 'N') t
set t.bonus = 0;
See this link for more details. http://geekswithblogs.net/WillSmith/archive/2008/06/18/oracle-update-with-join-again.aspx.
有关更多详细信息，请参阅此链接。 http://geekswithblogs.net/WillSmith/archive/2008/06/18/oracle-update-with-join-again.aspx。
Also, ensure that there are primary keys on all the tables you are joining.
此外，请确保您要加入的所有表上都有主键。
回答by Alexis Dufrenoy
As indicated here, the general syntax for the first solution proposed by Tony Andrews is :
如图所示这里，第一个解决方案由托尼·安德鲁斯提出的一般语法是：
update some_table s
set (s.col1, s.col2) = (select x.col1, x.col2
from other_table x
where x.key_value = s.key_value
)
where exists (select 1
from other_table x
where x.key_value = s.key_value
)
I think this is interesting especially if you want update more than one field.
我认为这很有趣，特别是如果您想要更新多个字段。
回答by Hemant
This following syntax works for me.
以下语法对我有用。
UPDATE
(SELECT A.utl_id,
b.utl1_id
FROM trb_pi_joint A
JOIN trb_tpr B
ON A.tp_id=B.tp_id Where A.pij_type=2 and a.utl_id is null
)
SET utl_id=utl1_id;
回答by user5299305
It works fine oracle
它工作正常 oracle
merge into table1 t1
using (select * from table2) t2
on (t1.empid = t2.empid)
when matched then update set t1.salary = t2.salary
回答by Janek Bogucki
Using descriptioninstead of desc for table2,
对 table2使用description而不是 desc，
update
table1
set
value = (select code from table2 where description = table1.value)
where
exists (select 1 from table2 where description = table1.value)
and
table1.updatetype = 'blah'
;
回答by afnhsn
UPDATE table1 t1
SET t1.value =
(select t2.CODE from table2 t2
where t1.value = t2.DESC)
WHERE t1.UPDATETYPE='blah';
