SQL Server 2008 update request with Join and Where clause in a joined table

Question

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'm trying to set the isUnsubscribed field to unsubscribed, where the userID in the UserSetting table is equal to userID in the user table and where emailAddress in the user table and not in the list of letters from another table. Can I run select in the isUnsubbed column using almost the same syntax and it works fine? thank you

PS I looked at other similar issues here, and the syntax looks the same, but obviously, I missed something.

Answer 1

Although the UPDATE ... FROM syntax is necessary in some cases, I prefer to use subqueries when possible. Does this do what you need?

 UPDATE UserSetting SET isUnsubscribedFromSystemEmails = 1 WHERE userID in (SELECT userID from [User] WHERE emailAddress in (SELECT emailAddress FROM BadEmailAddresses)) 

Answer 2

Yes, you missed something.

The set statement cannot reference an alias on the left side of the 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 a commented choice so that you can verify that you have earned the results you need.

Answer 3

Try the following:

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

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

Answer 5

Note. For the record only (provided that you earn the rest) you can also make an internal join in the BadEmailAddresses table.

If you have performance issues, you may need to index the emailAddress column in both tables.