CODEWITHSUNDEEP
mysqlsql
Dan P.
Dan P.

Reputation: 1775

Delete rows using JOIN

I have 2 tables that look like:

offlineconversations

userID | messageID

100      15
100      16
100      17

messages

messageID | sentBy | message               | ConvID
15          200      "Hi userID 100!"       hash
16          200      "Hi again 100!"        hash
17          300      "Hi I am user 300 :)"  hash

So here, UserID 100 received 3 messages, from user 200 and 300 (determined by sentBy in the messages table.

What I want to do, is deleted all rows in offlineconversations, where the message was sent by the user with id 200, so sentBy 200 in messages. The messageID in the offlineconversations table is associated with the messageID in messages Then offlineconversations would only be left with:

userID | messageID
100      17

So basically, delete from offlineconversations the rows where the userID is 100 and sentBy (200) is associated to them.

convID is used for the history aspects. If someone de-activates his account, the rows from messages should not be deleted. If the user deletes his account entirely, then the rows should be deleted from offlineconversations AND messages as well. That one I know I can do from cascading as someone pointed out in the comments.

Thanks

Upvotes: 1

Views: 97

Answers (2)

vp_arth
vp_arth

Reputation: 14992

This query should work:

 DELETE t.* 
 FROM offlineconversations t 
 JOIN messages m 
 ON m.messageID = t.messageID 
 WHERE m.sentBy = 200 AND t.userID = 100;

Fiddle

If you need to delete messages also:

 DELETE t.*,m.* 
 FROM offlineconversations t 
 JOIN messages m 
 ON m.messageID = t.messageID 
 WHERE m.sentBy = 200 AND t.userID=100;

Fiidle

Upvotes: 4

richardtz
richardtz

Reputation: 4993

what about :

    delete from offlineconversations where messageID in (select messageId from 
messages where sentBy = 200)

Upvotes: 3

Related Questions