SQL 根据每个表中的一列相等，使用另一表中的数据更新一个表中的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7918688/
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 rows in one table with data from another table based on one column in each being equal
提问by JcR49
Update many rowsinto one table from another table based on one column in each being equal (user_id).
根据每个表中的一列相等 (user_id)，将多行从另一个表更新到一个表中。
both tables have a user_idcolumn. Need to insert data from t2into t1when the user_idcolumn are equal.
两个表都有一user_id列。需要从数据插入t2到t1当user_id列相等。
Thank you in advance for any help offered.
预先感谢您提供的任何帮助。
回答by Dimitre Radoulov
update
table1 t1
set
(
t1.column1,
t1.column2
) = (
select
t2.column1,
t2.column2
from
table2 t2
where
t2.column1 = t1.column1
)
where exists (
select
null
from
table2 t2
where
t2.column1 = t1.column1
);
Or this (if t2.column1 <=> t1.column1 are many to one and anyone of them is good):
或者这个（如果 t2.column1 <=> t1.column1 是多对一并且其中任何一个都很好）：
update
table1 t1
set
(
t1.column1,
t1.column2
) = (
select
t2.column1,
t2.column2
from
table2 t2
where
t2.column1 = t1.column1
and
rownum = 1
)
where exists (
select
null
from
table2 t2
where
t2.column1 = t1.column1
);
回答by Tony Andrews
If you want to update matching rows in t1 with data from t2 then:
如果要使用 t2 中的数据更新 t1 中的匹配行，则：
update t1
set (c1, c2, c3) =
(select c1, c2, c3 from t2
where t2.user_id = t1.user_id)
where exists
(select * from t2
where t2.user_id = t1.user_id)
The "where exists" part it to prevent updating the t1 columns to null where no match exists.
“哪里存在”部分是为了防止在不存在匹配的情况下将 t1 列更新为 null。
回答by schurik
merge into t2 t2
using (select * from t1) t1
on (t2.user_id = t1.user_id)
when matched then update
set
t2.c1 = t1.c1
, t2.c2 = t1.c2
回答by Ollie
It's not an insert if the record already exists in t1 (the user_id matches) unless you are happy to create duplicate user_id's.
如果记录已存在于 t1（user_id 匹配）中，则它不是插入，除非您乐于创建重复的 user_id。
You might want an update?
你可能想要更新？
UPDATE t1
SET <t1.col_list> = (SELECT <t2.col_list>
FROM t2
WHERE t2.user_id = t1.user_id)
WHERE EXISTS
(SELECT 1
FROM t2
WHERE t1.user_id = t2.user_id);
Hope it helps...
希望能帮助到你...
回答by britishbaldeagle
You Could always use and leave out the "when not matched section"
您可以始终使用并省略“当不匹配部分”
merge into table1 FromTable
using table2 ToTable
on ( FromTable.field1 = ToTable.field1
and FromTable.field2 =ToTable.field2)
when Matched then
update set
ToTable.fieldr = FromTable.fieldx,
ToTable.fields = FromTable.fieldy,
ToTable.fieldt = FromTable.fieldz)
when not matched then
insert (ToTable.field1,
ToTable.field2,
ToTable.fieldr,
ToTable.fields,
ToTable.fieldt)
values (FromTable.field1,
FromTable.field2,
FromTable.fieldx,
FromTable.fieldy,
FromTable.fieldz);
