oracle SQL 根据与花药表的连接更新表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6892215/
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
SQL Update a table based on join with anther table
提问by Nomad
I am trying to update a table by joining the values with another table. Here's my query so far.
我正在尝试通过将值与另一个表连接来更新表。到目前为止，这是我的查询。
UPDATE LOGIN SET LOGIN.DISABLED_IND = 'N', LOGIN.DREASON = 'Test'
FROM CONTACT
WHERE CONTACT.CONTACT_ID = LOGIN.CONTACT_ID
AND CONTACT.RID ='abc'
When i run this, i get this
当我运行这个时，我得到了这个
[Error Code: 933, SQL State: 42000] ORA-00933: SQL command not properly ended
Thanks
谢谢
采纳答案by Justin Cave
If you expect to update a large fraction of the rows in LOGIN, it will likely be more efficient to use an EXISTS
如果您希望更新 中的大部分行LOGIN，使用 EXISTS 可能会更有效
UPDATE LOGIN l
SET l.DISABLED_IND = 'N',
l.DREASON = 'Test'
WHERE EXISTS (
SELECT 1
FROM CONTACT c
WHERE c.CONTACT_ID = l.CONTACT_ID
AND c.RID ='abc' )
If you are updating a relatively small fraction of the rows in LOGIN, Yahia's approach of using an IN would likely be more efficient
如果您要更新 中的一小部分行LOGIN，Yahia 使用 IN 的方法可能会更有效
UPDATE LOGIN l
SET l.DISABLED_IND = 'N',
l.DREASON = 'Test'
WHERE l.contact_id IN (
SELECT c.contact_id
FROM CONTACT c
WHERE c.RID ='abc' )
回答by Yahia
try
尝试
UPDATE LOGIN L SET L.DISABLED_IND = 'N', L.DREASON = 'Test'
WHERE L.CONTACT_ID
IN ( SELECT C.CONTACT_ID FROM CONTACT C WHERE C.CONTACT_ID = L.CONTACT_ID AND
C.RID='abc');
Another more complicated option see http://geekswithblogs.net/WillSmith/archive/2008/06/18/oracle-update-with-join-again.aspx
另一个更复杂的选项见http://geekswithblogs.net/WillSmith/archive/2008/06/18/oracle-update-with-join-again.aspx
回答by Alex Gitelman
Your syntax is not quite correct. This Wikipedia articlehas number of examples that you can choose from.
你的语法不太正确。这篇维基百科文章有许多示例供您选择。
For example
例如
UPDATE l
SET l.DISABLED_IND = 'N', l.DREASON = 'Test'
FROM LOGIN l
JOIN CONTACT c
ON c.CONTACT_ID = l.CONTACT_ID AND c.RID ='abc'
