Delete rows using JOIN

Question

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

presumably you have a foreign key relationship between those two tables? a cascading delete would take care of this, e.g. deleting (15/200) in messages would delete (100/15) in offlineconversations as well. — Marc B
– Marc B, Commented Apr 10, 2014 at 21:16
The thing is I don't want to delete the actual messages from messages, as I will use it for history if the contacts decide to become friends again. — Dan P.
– Dan P., Commented Apr 10, 2014 at 21:17
but if you delete (100/15) from offlineconversations, how could you re-associate that message with user 100? once 100/15 is gone, there'd be no recipient attached to messsage 15 anymore and no way to tell who it was sent to originally. — Marc B
– Marc B, Commented Apr 10, 2014 at 21:19
that'd imply you're re-using IDs, then. This is one reason re-using IDs is a bad idea. A new account should get a new ID, then this problem becomes moot. — Marc B
– Marc B, Commented Apr 10, 2014 at 21:26
but why would you need to change/delete anything in the messages on deactivation then? presumably that'd just be a single flag field in a users table, e.g. deactivated=1. no deleting or anything, just a simple update 0 => 1. — Marc B
– Marc B, Commented Apr 10, 2014 at 21:29

vp_arth · Accepted Answer · 2014-04-10 22:09:22Z

4

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

edited Apr 10, 2014 at 22:09

answered Apr 10, 2014 at 21:34

vp_arth

15k4 gold badges43 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Dan P. Over a year ago

Let's say that user 200 also sent to users 101, 102, 103 etc. Will it not delete the rows associated with these other contacts as well?

vp_arth Over a year ago

you need Foreign keys for this, or DELETE t.*, m.* ... or even DELETE *

Dan P. Over a year ago

Does your query delete all rows sent (to anybody, not just user 100) by user 200 in both tables?

vp_arth Over a year ago

not only in offlineconversations, if you want both, write DELETE *

Dan P. Over a year ago

Err, when I wrote my 'basically' sentence I made a mistake, I should of said: So basically, delete from offlineconversations the rows where the userID is 100 and sentBy (200) is associated to them.

|

richardtz · Accepted Answer · 2014-04-10 21:31:46Z

3

what about :

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

answered Apr 10, 2014 at 21:31

richardtz

4,9932 gold badges29 silver badges40 bronze badges

Collectives™ on Stack Overflow

Delete rows using JOIN

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related