14

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?

Scott M.
  • 7,313
  • 30
  • 39
Pr0no
  • 3,910
  • 21
  • 74
  • 121

4 Answers4

35

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'
Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
  • Thanks! And if I want to just view all tweets made by suspended accounts? I'm trying SELECT tweets.tweet JOIN users ON tweets.user_id = users.user_id WHERE users.name = 'SUSPENDED' but I get an error? "Check error at JOIN". Doesn't the same convention go for SELECT statements? – Pr0no Mar 31 '12 at 15:47
  • 1
    @Reveller you forgot your `FROM`: `SELECT tweet FROM tweets JOIN users ON tweets.user_id = users.user_id WHERE users.name = 'SUSPENDED'` – Michael Berkowski Mar 31 '12 at 15:49
  • @Michael Berkowski I have tried this type of query. But when records reach 30k Boundary server stopped. Is there any other solution? – Bhavin Chauhan Sep 27 '16 at 05:17
  • @BhavinChauhan You may not have indexes on the joined columns. Ensure both of them have an index or there is a foreign key relationship. – Michael Berkowski Sep 27 '16 at 10:57
  • 1
    @ Michael Berkowski Yes Thanks for comment, I have tried to add Primary key and it works like magic :) tested 5 lakh records. – Bhavin Chauhan Sep 28 '16 at 04:23
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.

VoteyDisciple
  • 37,319
  • 5
  • 97
  • 97
0

Started answering before verifying the type of server you are using. I know this works in MS SQL Server. I am not sure about MySQL however. Don't have MySQL installed so give it a try.

UPDATE tweets SET
  spam = 1
FROM tweets INNER JOIN users ON users.user_id = tweets.user_id
WHERE users.name = 'SUSPENDED'
Dan P
  • 1,939
  • 3
  • 17
  • 30
-1

Suppose, you have 3 tables and need to update one column from one table with another table. There are several ways to do this. Please look into this query for example. 

update Table1 T1, Table2 T2, Table3 T3 set T1.field_name = T3.field_name where (T1.field_name = T2.field_user_name) and (T2.field_id = T3.entity_id);

Relation between tables must be maintained carefully.

Thomas
  • 1,445
  • 14
  • 30
Md Jamiruddin
  • 17
  • 2