在连接表中使用Join和Where子句的SQL Server 2008更新查询

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 isUnsubscribed field to unsubscribed where the userID in the UserSetting table equals the userID in the user table and where the emailAddress in 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字段设置为unsubscribed，其中UserSetting表中的userID等于user表中的userID，而user表中的emailAddress不在另一个表的电子邮件列表中。我可以使用几乎相同的语法在isUnsubbed列上运行select并且它工作正常吗？谢谢！

P.S. I've looked at other similar questions here and the syntax appears the same but obviously I'm missing something.

附：我在这里看了其他类似的问题，语法看起来一样，但显然我错过了一些东西。

5 个解决方案

#1

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))

#2

Yep you've overlooked something.

你是否忽略了某些东西。

The set statement cannot reference the alias on the left side of the 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.

我添加了注释掉的选项，以便您可以检查是否正在查看您想要的结果集。

#3

Try this :

尝试这个 ：

UPDATE UserSetting ust SET usr.isUnsubscribedFromSystemEmails = 1
WHERE ust.emailAdress IN (select emailAddress from bademailAddresses);

#4

Try:

尝试：

UPDATE  
    UserSetting
SET   
    isUnsubscribedFromSystemEmails = 1 
FROM          
    UserSetting 
INNER JOIN 
    [User] ON UserSetting.userID = [User].userID  
AND  
    [User].emailAddress IN (SELECT emailAddress FROM BadEmailAddresses)

#5

Note: Just for the record (assuming you get everything else to work), you could also do an inner join on the BadEmailAddresses table.

注意：只是为了记录（假设您完成其他所有工作），您还可以在BadEmailAddresses表上进行内部联接。

If you have any performance problems, then you might want to index the emailAddress column in both tables.

如果您有任何性能问题，那么您可能希望索引两个表中的emailAddress列。