oracle 使用另一个表中的值更新表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45568650/
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 a table with values from another table
提问by hashir
I have a table (say,ABC) which has the following structure :
我有一张表（比如 ABC），它具有以下结构：
COMP_CODE NAME SALARY SID1
-------------------------------------
NULL Alex 42000 85
NULL Alex 42000 89
NULL Alex 42000 96
NULL Alex 42000 100
NULL Alex 42000 52
I want to update the _COMP_CODE_ column in the above table from value residing in another table (CC). The matching columns among the two table is SID1. The structure of CCis as follows :
我想从位于另一个表(CC) 中的值更新上表中的 _ COMP_CODE_ 列。两个表中匹配的列是SID1。CC的结构如下：
COMP_CODE SID1
----------------------
0AA 85
0AB 96
0CD 98
0DE 72
0EH 100
Can this achieved through a CURSORor any other sort of method.
这可以通过CURSOR或任何其他类型的方法来实现。
采纳答案by S?ren Kongstad
MERGE INTO ABC t1
USING (select SID,max(COMP_CODE) COMP_CODE from CC GROUP BY SID) t2
ON (t1.SID1= t2.SID1)
WHEN MATCHED THEN
UPDATE SET t1.COMP_CODE = t2.COMP_CODE
回答by Gordon Linoff
First, there is no need to store the data twice. You can just fetch it using a JOINwhen you need it.
首先，不需要将数据存储两次。您可以JOIN在需要时使用 a 来获取它。
You can update the values as:
您可以将值更新为：
update abc
set comp_code = (select cc.comp_code
from cc
where cc.sid1 = abc.sid1
);
This will update all rows in abc. If sid1does not match in the second table, then the value will remain NULL.
这将更新abc. 如果sid1在第二个表中不匹配，则该值将保持为NULL。
EDIT:
编辑：
Your second table has multiple rows with the same value. You need to figure out which one you want. To choose an arbitrary one:
您的第二个表有多行具有相同的值。你需要弄清楚你想要哪一个。选择任意一个：
update abc
set comp_code = (select cc.comp_code
from cc
where cc.sid1 = abc.sid1 and rownum = 1
);
You can also choose MIN(), MAX(), LISTAGG()or some other combination of values.
您也可以选择MIN()，MAX()，LISTAGG()或价值的其他组合。
