CODEWITHSUNDEEP
mysqlsql-delete
Liam
Liam

Reputation: 9855

Delete from 2 tables

I have 2 tables in my DB

users...

ID    Username    Randomkey
1     Dionne      938493
2     Liam        902303
3     James       232039
4     Sarah       320923

interests...

ID    User_ID    Interest
1     3          Army
2     3          Boxing
3     3          Tennis
4     4          Make Up

In my interests table, 'User_ID' is equal to 'ID' from my users table.

I want to write a query that when triggered, deletes the row from my 'users' table and at the same time deletes all data relating to the user from the interests table.

I'm unsure how to do this and I've had no luck with Google.

If anybody could give me some advice or at least point me where to find this information it would be a great help.

In my head I'm thinking it would be something along the lines of: 

DELETE FROM users, interests WHERE ID = '$userID' AND User_ID = '$userID'

Upvotes: 2

Views: 157

Answers (2)

Ted Hopp
Ted Hopp

Reputation: 234795

If you're using InnoDB, then you can define a foreign key constraint for interests (User_ID) that references users.ID and has ON DELETE CASCADE. Then you just have to delete the row from users and all the rows in interests that reference that user will automatically be deleted.

If you're not using InnoDB, then Wrikken's solution works fine.

Upvotes: 3

Wrikken
Wrikken

Reputation: 70460

DELETE users, interests 
FROM users 
LEFT JOIN interests ON users.ID = interests.User_ID
WHERE users.ID = <id>;

Of course, if the interests table was InnoDB & had a foreign key constraint to users with an ON DELETE CASCADE this would be done automatically when a user gets deleted.

Upvotes: 6

Related Questions