Oracle SQL：用另一个表中的数据更新一个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7030699/
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 SQL: Update a table with data from another table
提问by Muhd
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
In oracle SQL, how do I run an sql updatequery that can update Table 1 with Table 2's nameand descusing the same id? So the end result I would get is
在 oracle SQL 中，如何运行可以使用表 2 更新表 1并使用相同的sql 更新查询？所以我得到的最终结果是namedescid
Table 1:
表格1：
id name desc
-----------------------
1 x 123
2 y 345
3 c adf
Question is taken from update one table with data from another, but specifically for oracle SQL.
问题来自用另一个表更新一个表，但专门针对 oracle SQL。
回答by Justin Cave
This is called a correlated update
这称为相关更新
UPDATE table1 t1
SET (name, desc) = (SELECT t2.name, t2.desc
FROM table2 t2
WHERE t1.id = t2.id)
WHERE EXISTS (
SELECT 1
FROM table2 t2
WHERE t1.id = t2.id )
Assuming the join results in a key-preserved view, you could also
假设联接结果为保留键的视图，您还可以
UPDATE (SELECT t1.id,
t1.name name1,
t1.desc desc1,
t2.name name2,
t2.desc desc2
FROM table1 t1,
table2 t2
WHERE t1.id = t2.id)
SET name1 = name2,
desc1 = desc2
回答by Adrian
Try this:
尝试这个：
MERGE INTO table1 t1
USING
(
-- For more complicated queries you can use WITH clause here
SELECT * FROM table2
)t2
ON(t1.id = t2.id)
WHEN MATCHED THEN UPDATE SET
t1.name = t2.name,
t1.desc = t2.desc;
回答by Yahia
try
尝试
UPDATE Table1 T1 SET
T1.name = (SELECT T2.name FROM Table2 T2 WHERE T2.id = T1.id),
T1.desc = (SELECT T2.desc FROM Table2 T2 WHERE T2.id = T1.id)
WHERE T1.id IN (SELECT T2.id FROM Table2 T2 WHERE T2.id = T1.id);
回答by Pau Karr
Update table set column = (select...)
never worked for me since set only expects 1 value - SQL Error: ORA-01427: single-row subquery returns more than one row.
从来没有对我来说有效过，因为 set 只需要 1 个值 - SQL 错误：ORA-01427：单行子查询返回多于一行。
here's the solution:
这是解决方案：
BEGIN
For i in (select id, name, desc from table1)
LOOP
Update table2 set name = i.name, desc = i.desc where id = i.id;
END LOOP;
END;
That's how exactly you run it on SQLDeveloper worksheet. They say it's slow but that's the only solution that worked for me on this case.
这就是您在 SQLDeveloper 工作表上运行它的方式。他们说它很慢，但这是在这种情况下对我有用的唯一解决方案。
回答by ant
Here seems to be an even better answer with 'in' clause that allows for multiple keys for the join:
'in' 子句似乎是一个更好的答案，它允许连接有多个键：
update fp_active set STATE='E',
LAST_DATE_MAJ = sysdate where (client,code) in (select (client,code) from fp_detail
where valid = 1) ...
The beef is in having the columns that you want to use as the key in parentheses in the where clause before 'in' and have the select statement with the same column names in parentheses. where (column1,column2) in ( select (column1,column2) from table where "the set I want");
问题在于，在“in”之前的 where 子句中，将要用作括号中的列作为键，并在括号中使用具有相同列名的 select 语句。where ( column1,column2) in ( select ( column1,column2) from table where "the set I want");
回答by Jim P
If your table t1 and it's backup t2 have many columns, here's a compact way to do it.
如果你的表 t1 和它的备份 t2 有很多列，这里有一个紧凑的方法来做到这一点。
In addition, my related problem was that only some of the columns were modified and many rows had no edits to these columns, so I wanted to leave those alone - basically restore a subset of columns from a backup of the entire table. If you want to just restore all rows, skip the where clause.
此外，我的相关问题是只有部分列被修改，许多行没有对这些列进行编辑，所以我想不理会这些——基本上是从整个表的备份中恢复列的子集。如果您只想恢复所有行，请跳过 where 子句。
Of course the simpler way would be to delete and insert as select, but in my case I needed a solution with just updates.
当然，更简单的方法是删除和插入作为选择，但在我的情况下，我需要一个仅更新的解决方案。
The trick is that when you do select * from a pair of tables with duplicate column names, the 2nd one will get named _1. So here's what I came up with:
诀窍是，当您从一对具有重复列名的表中选择 * 时，第二个表将被命名为 _1。所以这就是我想出的：
update (
select * from t1 join t2 on t2.id = t1.id
where id in (
select id from (
select id, col1, col2, ... from t2
minus select id, col1, col2, ... from t1
)
)
) set col1=col1_1, col2=col2_1, ...
回答by Avila Theresa
BEGIN
For i in (select id, name, desc from table2)
LOOP
Update table1 set name = i.name, desc = i.desc where id = i.id and (name is null or desc is null);
END LOOP;
END;
