MySQL delete FROM multiple tables where key can be found in different columns in the same table

Question

I have multiple tables holding infos about users and content.

Table users: 
id     name
 5     foo
33     abc

Table imgs:
id     uid    img_name
1       5        bar
8      33        xy

Table user_permissions: 
id     uid    permission_id
1      5         3
2      3         3

Table liked_content: 
id     contente_id    content_holder_id     likedby_id
1           8                33                   5
2           1                 5                  56

If a user is deleted I want to delete all rows in all tables related to his user-id. This works fine if I add only one column per table.

DELETE users,
       imgs,
       user-permissions,
       liked_content
FROM users,
     imgs,
     user-permissions,
     liked_content
WHERE imgs.uid = users.id AND
      user_permissions.uid = users.id AND
      liked_content.content_holder_id = users.id AND
      users.id  = 5

Adding a second row in the WHERE-clause (likedby_id/liked_content-table) where the id can be found will not work.

...
WHERE imgs.uid = users.id AND
      user_permissions.uid = users.id AND
      liked_content.content_holder_id = users.id AND
      liked_content.likedby_id = users.id AND
      users.id  = 5

What would be the correct way adding a second column to the query for a table already listed?

score 0 · Answer 1 · edited May 23 '17 at 11:46

0

What you're implementing yourself is a cascading delete. It is generally built into the DB engine. Check out this related question: MySQL foreign key constraints, cascade delete If you were dead set on doing it yourself, I'd recommend multiple delete statements within a transaction that traverse your schema from child to parent:

DELETE 
FROM liked_content
WHERE content_holder_id = 5;

DELETE 
FROM imgs
WHERE uid = 5;

DELETE 
FROM user-permissions
WHERE uid = 5;

DELETE 
FROM users
WHERE id = 5;

edited May 23 '17 at 11:46

Community

1
1

answered Mar 26 '14 at 16:42

Andreas

4,937
2
25
35

Thank you. @chameera-solution worked for me. Maybe using foreign key constraint along with cascade delete would be the proper way bud I could not implement it. – uvo Mar 26 '14 at 19:30

score 0 · Accepted Answer · answered Mar 26 '14 at 17:40

According to your data, user id can be in liked_content table as either content_holder_id OR likedby_id. Not necessarily as both. Where clause should change as:

...
  WHERE imgs.uid = users.id AND
  user_permissions.uid = users.id AND
  (liked_content.content_holder_id = users.id OR
  liked_content.likedby_id = users.id) AND
  users.id  = 5

If you use foreign key constraint along with cascade delete rule, you do not have to do this manually.

Perfect! I tried OR before bud without parentheses and it did not work. — uvo, Mar 26 '14 at 19:30

score 0 · Answer 3 · answered Feb 23 '17 at 20:58

The technically correct way, as others have stated, is to setup cascading deletes.

I would not willingly do that if I was in charge of the database, simply because you will find it to be very painful trying to manipulate the tables and especially when you are trying to create sample data.

I would go for the KISS method...

Do a DELETE for each subordinate table individually referring to the users id, then delete the master record.

If you do the foreign key and cascading thing, you will not like what it does to your content table, since it has two relations to the master user table.

It means, for example, if you are attempting to delete content rows for holder_id 5, you will first have to disconnect those rows from their associated likedby_id master record by setting the likedby_id to NULL, otherwise the cascade would delete the likedby_id master record. But, it also won't allow you to NULL the likedby_id because doing so would invalidate the foreign key relations you need to actually do the cascade.

Do yourself a favor - Go simple.

MySQL delete FROM multiple tables where key can be found in different columns in the same table

3 Answers3