SQL Server 2008 Update Query with Join and Where clause in 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 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!

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

why it's not working ? do you have an error message (if so, what's the message?) or you don't have the expected results (if so, what are the obtained results ?)? — remi bourgarel, Sep 20 '10 at 15:23
hit enter by mistake. Column or expression 'isUnsubscribedFromSystemEmails' cannot be updated. The columns exists and is writable.. no permissions issue.. and then i get some other stuff and the error message is : invalid object name 'ust'. UserSetting table definitely exists! Select using same join and where in clause gives correct results — toddm, Sep 20 '10 at 15:38
@toddm: "and where the `emailAddress` in the user table is **not** in a list of emails from another table" - do you mean `BadEmailAddresses`, and if so, do you mean "and where the `emailAddress` in the user table **is** in a list of emails from `BadEmailAddresses` "? — , Sep 20 '10 at 15:46
Just a comment regarding the column name 'isUnsubscribedFromSystemEmails'. Aside from being a bit long, I try to avoid negative names. I'd prefer something like 'SendSystemEmails' — Andrew Carmichael, Sep 20 '10 at 15:54
hi Paul, i find you can never be explicit enough with names. What not make it nice and descriptive? As entity names this helps developers understand what the properties do without guessing. As for the negative names. i do agree.. although in this case it is negative if they unsub! ;') — toddm, Sep 20 '10 at 17:11

HLGEM · Answer 1 · 2010-09-20T16:49:55.237

10

Yep you've overlooked something.

The set statement cannot reference the 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 the commented out select so you can check to see that you aregetting results set you wanted.

edited Sep 20 '10 at 16:49

answered Sep 20 '10 at 15:23

HLGEM

94,695
15
113
186

"Unable to parse query Text" Invalid object name 'ust' – toddm Sep 20 '10 at 15:32
select * FROM UserSetting AS ust INNER JOIN [User] ON ust.userID = [User].userID AND [User].emailAddress IN (SELECT emailAddress FROM BadEmailAddresses) works just fine by the way! returns the expected results?? – toddm Sep 20 '10 at 15:40
If the select worked fine, I'm at a loss. Why don;t you govie us some sample data from User settings, User and and bademailaddresses and we can test against the data you are expecting to have. – HLGEM Sep 20 '10 at 16:51
I tried what I gave you on my machine and it parses and runs. are you sure you used what I did exactly? (Ichanged the last and to a where but that is functionally the same thing and both versions work, I just prefer to use the join only for what is needed for the join). The key is changing the ust.isUnsubscribedFromSystemEmails = 1 to isUnsubscribedFromSystemEmails = 1 – HLGEM Sep 20 '10 at 17:03

score 2 · Accepted Answer · answered Sep 20 '10 at 15:50

2

Although the UPDATE...FROM syntax is essential in some circumstances, I prefer to use subqueries whenever 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))

answered Sep 20 '10 at 15:50

Andrew Carmichael

3,086
1
22
21

this works. thanks! Subsequently updates the set twice though.. but at this point i could care less. thanks! – toddm Sep 20 '10 at 17:09

score 0 · Answer 3 · edited Sep 06 '12 at 18:50

0

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

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

edited Sep 06 '12 at 18:50

Michael Petrotta

59,888
27
145
179

answered Sep 06 '12 at 16:24

Henry Stinson

1

2

Actually, an `INNER JOIN` is less performant than a sub-query when the sub-query is concise like the one in the question. A more appropriate extension to this would be to turn `[User]` into a view that ran the sub-query in the `WHERE` clause. – Mike Perrenoud Sep 26 '12 at 13:39

score 0 · Answer 4 · answered Sep 20 '10 at 15:24

0

Try this :

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

answered Sep 20 '10 at 15:24

ykatchou

3,667
1
22
27

i tried this.. for one the isUnsubbed column is in the ust table.. even with this change the query does not parse. – toddm Sep 20 '10 at 15:32

score 0 · Answer 5 · answered Sep 20 '10 at 15:42

0

Try:

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

answered Sep 20 '10 at 15:42

this sets every single record in UserSetting.isUnsubscribedFromSystemEmails to true – toddm Sep 20 '10 at 16:41
So every single user email is bad? – HLGEM Sep 20 '10 at 16:52

SQL Server 2008 Update Query with Join and Where clause in joined table

5 Answers5