How to join two tables in an UPDATE statement?

Question

Consider the following tables: users and tweets

user_id name tweet_id user_id tweet spam ----------------- ---------------------------------- 1 SUSPENDED 1 1 lorem ipsum 0 2 foo 2 1 dolor 0 3 bar 3 2 samet 0 4 SUSPENDED 4 1 stunitas 0 5 3 hello 0 6 4 spamzz! 0 

I want to update the “tweets” table, marking all tweets made by SUSPENDED users as spam. Thus, in the above example, tweets with tweet_id 1, 2, 4, and 6 will be marked as spam, updating the spam value from 0 to 1.

I am having problems joining two tables. So far, I have only had to join SELECT statements, but this seems more difficult:

 UPDATE tweets SET spam = 1 WHERE tweets.user_id = users.user_id AND users.name = 'SUSPENDED' 

This, of course, does not work ... who can point me in the right direction?

Answer 1

You are on the right track, but you need to specify the JOIN between the tables:

 UPDATE tweets JOIN users ON tweets.user_id = users.user_id SET tweets.spam = 1 WHERE users.name = 'SUSPENDED' 

Answer 2

This should do it:

 UPDATE tweets INNER JOIN users ON (users.user_id = tweets.user_id) SET spam=1 WHERE users.name='SUSPENDED' 

Usually you can use JOIN in UPDATE same as in SELECT . You wouldn't be able to join the table for yourself on UPDATE , and there are some other small quirks, but for such basic things, this will work as you would expect.

Answer 3

I began to respond before checking the type of server you are using. I know this works in MS SQL Server. However, I'm not sure about MySQL. Do not install MySQL, so try it.

 UPDATE tweets SET spam = 1 FROM tweets INNER JOIN users ON users.user_id = tweets.user_id WHERE users.name = 'SUSPENDED'