oracle 通过另一表中的列更新一个表中的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12267329/
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
Updating a column in one table through a column in another table
提问by Snow Leopard
I have 2 tables 'A' and 'B'. Both have a common column 'name' and linked with each other by a column 'id'. The column 'name' in the table A is empty whereas it has data in table B. My task is to fill all the data in that column from table B to table A with corresponding ids.
我有 2 张桌子“A”和“B”。两者都有一个共同的列“名称”，并通过列“id”相互链接。表 A 中的列 'name' 是空的，而它在表 B 中有数据。我的任务是用相应的 id 填充从表 B 到表 A 的该列中的所有数据。
I'm using the following query:
我正在使用以下查询：
UPDATE A
SET A.name = (SELECT B.name from B WHERE A.id = B.id)
WHERE EXISTS
(SELECT B.name from B)
As soon as I run the query in SQL developer, it just freezes and i have to force close it. Can anyone tell me what's wrong with the query?
只要我在 SQL 开发人员中运行查询，它就会冻结，我必须强制关闭它。谁能告诉我查询有什么问题？
回答by Leniel Maccaferri
Try this one instead:
试试这个：
UPDATE A
SET A.name = (SELECT B.name FROM B WHERE B.id = A.id AND B.name IS NOT NULL)
WHERE a.name IS NULL;
Since you're using Oracle, here's the reference for IS NOT NULL.
由于您使用的是 Oracle，这里是IS NOT NULL的参考。
Here's the SQL Fiddle so that you can play with it: http://sqlfiddle.com/#!4/a5ad0/3
这是 SQL Fiddle，您可以使用它：http: //sqlfiddle.com/#!4/a5ad0/3
回答by David Faber
I'm not sure from the above conversation whether you made any changes beyond indexing your data, but you should include a WHERE EXISTSclause as mentioned. The complete query should look like this:
从上面的对话中，我不确定您是否对数据进行了索引之外的任何更改，但是您应该包含一个WHERE EXISTS提到的子句。完整的查询应如下所示：
UPDATE A
SET A.name = ( SELECT B.name FROM B WHERE B.id = A.id )
WHERE EXISTS ( SELECT 1 FROM B WHERE B.id = A.id )
The WHERE EXISTSclause in your original query won't do much of anything except check to see if there is at least one non-NULL value of namein B.
WHERE EXISTS原始查询中的子句除了检查namein是否至少有一个非 NULL 值外，不会做任何事情B。
