SQL Oracle：如何更新不同表中的多个列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37469361/
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
Oracle : How to update multiple columns from different table?
提问by Sivaraj Thavamani
I am using oracle database and have a situations to update fields from some other tables. My issue is it is updating all the records instead of specified conditions. For example, I am trying to update perm_address and temp_address in EMPLOYEE table from ADDRESS table. Right now, I am using below query. But, it is updating all the records.
我正在使用 oracle 数据库，并且需要更新其他一些表中的字段。我的问题是它正在更新所有记录而不是指定条件。例如，我试图从 ADDRESS 表更新 EMPLOYEE 表中的 perm_address 和 temp_address。现在，我正在使用以下查询。但是，它正在更新所有记录。
UPDATE EMPLOYEE EMP
SET (EMP.PERM_ADDRESS, EMP.TEMP_ADDRESS) =
(SELECT ADDR.PERM_ADDR,ADDR.TEMP_ADDR
FROM ADDRESS ADDR
WHERE ADDR.ID=EMP.ADDRESS_ID
);
In Oracle how to handle this situations? Normally, how to handle the update from multiple table into source table?
在Oracle中如何处理这种情况？通常，如何处理从多个表到源表的更新？
Thanks in advance....
提前致谢....
回答by Gordon Linoff
Add a WHEREclause to update only matching records:
添加WHERE子句以仅更新匹配的记录：
UPDATE EMPLOYEE EMP
SET (EMP.PERM_ADDRESS, EMP.TEMP_ADDRESS) =
(SELECT ADDR.PERM_ADDR, ADDR.TEMP_ADDR
FROM ADDRESS ADDR
WHERE ADDR.ID = EMP.ADDRESS_ID
)
WHERE EXISTS (SELECT 1 FROM ADDRESS ADDR WHERE ADDR.ID = EMP.ADDRESS_ID);
回答by mathguy
Updating a table with data from another table is often simpler using the MERGE statement. https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm
使用 MERGE 语句用另一个表中的数据更新一个表通常更简单。https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm
Something like this:
像这样的东西：
merge into employee emp
using address addr
on (addr.id = emp.address_id)
when matched
then update
set emp.perm_address = addr.perm_addr,
emp.temp_address = addr.temp_addr;
回答by Jenna Leaf
updating one table by another table - the basic format is
用另一个表更新一个表 - 基本格式是
--ORACLE
update tableX t set (t.fldA, t.fldB) =
(select fldA, fldB from table_B where ID ='X')
where t.ID = 'Y'
