SQL 用来自另一个表的数据更新一个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5036918/
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 one table with data from another
提问by Patrick
Table 1:
表格1：
id name desc
-----------------------
1 a abc
2 b def
3 c adf
Table 2:
表 2：
id name desc
-----------------------
1 x 123
2 y 345
How do I run an sql updatequery that can update Table 1 with Table 2's name and desc using the same id? So the end result I would get is
如何运行可以使用相同 ID 使用表 2 的名称和 desc 更新表 1的sql 更新查询？所以我得到的最终结果是
Table 1:
表格1：
id name desc
-----------------------
1 x 123
2 y 345
3 c adf
How can this be done for:
如何做到这一点：
- SQL Server
- MySQL
- PostgreSQL
- Oracle
- 数据库服务器
- MySQL
- PostgreSQL
- 甲骨文
回答by The Scrum Meister
For MySql:
对于 MySQL：
UPDATE table1 JOIN table2
ON table1.id = table2.id
SET table1.name = table2.name,
table1.`desc` = table2.`desc`
For Sql Server:
对于 SQL Server：
UPDATE table1
SET table1.name = table2.name,
table1.[desc] = table2.[desc]
FROM table1 JOIN table2
ON table1.id = table2.id
回答by user272735
Oracle 11g R2:
甲骨文 11g R2：
create table table1 (
id number,
name varchar2(10),
desc_ varchar2(10)
);
create table table2 (
id number,
name varchar2(10),
desc_ varchar2(10)
);
insert into table1 values(1, 'a', 'abc');
insert into table1 values(2, 'b', 'def');
insert into table1 values(3, 'c', 'ghi');
insert into table2 values(1, 'x', '123');
insert into table2 values(2, 'y', '456');
merge into table1 t1
using (select * from table2) t2
on (t1.id = t2.id)
when matched then update set t1.name = t2.name, t1.desc_ = t2.desc_;
select * from table1;
ID NAME DESC_
---------- ---------- ----------
1 x 123
2 y 456
3 c ghi
回答by Awais Afridi
UPDATE table1
SET
`ID` = (SELECT table2.id FROM table2 WHERE table1.`name`=table2.`name`)
回答by Chandramani
Try following code. It is working for me....
尝试以下代码。它对我有用......
UPDATE TableOne
SET
field1 =(SELECT TableTwo.field1 FROM TableTwo WHERE TableOne.id=TableTwo.id),
field2 =(SELECT TableTwo.field2 FROM TableTwo WHERE TableOne.id=TableTwo.id)
WHERE TableOne.id = (SELECT TableTwo.id
FROM TableTwo
WHERE TableOne.id = TableTwo.id)
回答by Gil Baggio
Use the following block of query to update Table1 with Table2 based on ID:
使用以下查询块根据 ID 用 Table2 更新 Table1：
UPDATE Table1, Table2
SET Table1.DataColumn= Table2.DataColumn
where Table1.ID= Table2.ID;
This is the easiest and fastest way to tackle this problem.
这是解决这个问题的最简单、最快捷的方法。
