DELETE rows using SELECT results – part 2

Theres already a post here about deleting rows from some table using results from a different query. This is basically the same but more raw/basic.

So i have:

– a table ‘events’ with id_event,title, etc.
– a table ‘users’ with id_user, name, etc.
– a table ‘rel_users_events’, which is a relationship between ‘users’ and ‘events’, with id, id_user,id_event

Lets assume i want to remove a user (id_user=1) from the system and all his events.

We can do this:

DELETE FROM events
WHERE id_event IN
(SELECT id_event FROM rel_users_events WHERE id_user=’1′)

This first executes the subquery SELECT, getting a list of all the event ids that user 1 has. Then it uses that list to delete all those event ids from the table ‘events’.

NOTE: This problem could be solved using other methods, obviously, like procedures, programming, magic, etc. but i wanted to show a pure SQL approach.

To read:

– First post about DELETE using SELECT