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 by marking all tweets made by SUSPENDED users, as spam. So in the above example, tweets with tweet_id 1, 2, 4 and 6 would be marked as spam by updating the "spam" value from 0 to 1.

I'm having trouble joining the two tables. Until now, I've only had to join in SELECT statements, but this seems more troublesome:

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

This surely isn't working...who could point me in the right direction?

Answer 1

You're on the right track, but you need to specify a 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'

You can generally use JOIN the same in an UPDATE as you can in a SELECT. You wouldn't be able to join a table to itself in an UPDATE, and there are some other little quirks, but for basic stuff like this it'll work as you'd expect.