SQL Server 2008 使用联接表中的联接和 Where 子句更新查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3752876/
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 Server 2008 Update Query with Join and Where clause in joined table
提问by toddm
Not sure why this is not working:
不知道为什么这不起作用：
UPDATE
ust
SET
ust.isUnsubscribedFromSystemEmails = 1
FROM
UserSetting AS ust
INNER JOIN
[User] ON ust.userID = [User].userID
AND
[User].emailAddress IN (SELECT emailAddress FROM BadEmailAddresses)
In plain English, I am trying to set the isUnsubscribedfield to unsubscribed where the userIDin the UserSettingtable equals the userIDin the user table and where the emailAddressin the user table is not in a list of emails from another table. I can run a select on the isUnsubbed column using pretty much the same syntax and it works fine? thanks!
用简单的英语，我想在设置isUnsubscribed字段退订，其中userID在UserSetting表等于userID在user表并且其中emailAddress在用户表不是从另一个表电子邮件的列表。我可以使用几乎相同的语法在 isUnsubbed 列上运行选择并且它工作正常吗？谢谢！
P.S.I've looked at other similar questions here and the syntax appears the same but obviously I'm missing something.
PS我在这里查看了其他类似的问题，语法看起来相同，但显然我遗漏了一些东西。
采纳答案by Paul Spangle
Although the UPDATE...FROM syntax is essential in some circumstances, I prefer to use subqueries whenever possible. Does this do what you need?
尽管 UPDATE...FROM 语法在某些情况下是必不可少的，但我更喜欢尽可能使用子查询。这能满足您的需求吗？
UPDATE UserSetting
SET isUnsubscribedFromSystemEmails = 1
WHERE userID in (SELECT userID from [User]
WHERE emailAddress in (SELECT emailAddress FROM BadEmailAddresses))
回答by HLGEM
Yep you've overlooked something.
是的，你忽略了一些东西。
The set statement cannot reference the alias on the left side of the set.
set 语句不能引用 set 左侧的别名。
Try:
尝试：
UPDATE
ust
SET
isUnsubscribedFromSystemEmails = 1
--select *
FROM
UserSetting AS ust
INNER JOIN
[User] ON ust.userID = [User].userID
WHERE [User].emailAddress IN (SELECT emailAddress FROM BadEmailAddresses)
I added the commented out select so you can check to see that you aregetting results set you wanted.
我添加了注释掉的选择，以便您可以检查是否获得了您想要的结果集。
回答by Henry Stinson
Note: Just for the record (assuming you get everything else to work), you could also do an inner join on the BadEmailAddressestable.
注意：只是为了记录（假设您让其他一切工作），您还可以在BadEmailAddresses表上进行内部联接。
If you have any performance problems, then you might want to index the emailAddresscolumn in both tables.
如果您有任何性能问题，那么您可能希望为emailAddress两个表中的列建立索引。
回答by ykatchou
Try this :
尝试这个 ：
UPDATE UserSetting ust SET usr.isUnsubscribedFromSystemEmails = 1
WHERE ust.emailAdress IN (select emailAddress from bademailAddresses);
回答by ykatchou
Try:
尝试：
UPDATE
UserSetting
SET
isUnsubscribedFromSystemEmails = 1
FROM
UserSetting
INNER JOIN
[User] ON UserSetting.userID = [User].userID
AND
[User].emailAddress IN (SELECT emailAddress FROM BadEmailAddresses)
