Delete multiple rows from multiple tables in mysql using single value

Question

I have a number of mysql tables with different table structures and all they have the followings table names and fieldnames (the ones of the left are table names and on the right are field names)

I want to be able to delete the values of the rows using a single variable (example $id = somename) in a single query

    user  id,
    user_image  user
    user_interest  user 
    user_lang  id 
    user_login  user
    user_personal  user
    user_prefer user
    user_reviewuser
    user_role user_id
    user_translang  user
    user_translate  user
    user_web  id

Also, a particular userid may not be present across all tables.

I am not sure at all on how to delete them all at once. Any tips would be appreciated, i looked at a couploe of similar questions but couldn't find a proper answer.

forign key index would make this a snap

user557846
– user557846

2014-10-02 20:56:30 +00:00
Commented Oct 2, 2014 at 20:56 — user557846
– user557846, Commented Oct 2, 2014 at 20:56

Derek Kurth · Accepted Answer · 2014-10-02 20:58:28Z

3

You want to create a foreign key constraint, and specify "ON DELETE CASCADE". You'll need this for each of your tables that has a foreign key to user. For example,

ALTER TABLE user_image
ADD CONSTRAINT fk_user_image_user FOREIGN KEY (user)
    REFERENCES user(id)
    ON DELETE CASCADE;

answered Oct 2, 2014 at 20:58

Derek Kurth

1,9181 gold badge20 silver badges19 bronze badges

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

2 Comments

Bazinga777 Over a year ago

Thanks, Will it matter if any of those tables dont have the user in their tables ?

Barmar Over a year ago

No. If there's nothing to delete, nothing will be deleted.

Barmar · Accepted Answer · 2014-10-02 21:07:33Z

2

You can use the multi-table DELETE syntax. You need to use a LEFT JOIN in case any of the related tables do not have a corresponding row.

DELETE user, user_image, user_interest, ...
FROM user
LEFT JOIN user_image ON user.id = user_image.user
LEFT JOIN user_interest ON user.id = user_interest.user
...
WHERE user.id = $id

DEMO

However, if you relate the tables using a foreign key constraint, with ON DELETE CASCADE, you can just delete the row from the user table, and all the related rows will be deleted.

edited Oct 2, 2014 at 21:07

answered Oct 2, 2014 at 20:59

Barmar

789k57 gold badges554 silver badges669 bronze badges

1 Comment

Bazinga777 Over a year ago

Thanks, ON DELETE CASCADE is a better option I GUESS.

Collectives™ on Stack Overflow

Delete multiple rows from multiple tables in mysql using single value

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related