MySQL Delete From Multiple Tables in One Query. We always used to connect more than two tables with grouping information’s. like user credentials on one table and his posts and relevant information’s on other table. When we need to delete a user, we don’t need that data to keep in other tables. so we need to delete those data’s in other tables as well. We may have the following ways to remove those details from different tables.
- Multiple Delete queries
- Delete with inner join
- Delete with left join
Multiple Delete Queries :
We can use multiple delete queries together to delete in deferent tables.
begin transaction DELETE FROM users WHERE id=2 DELETE FROM employee WHERE user_id=2 DELETE FROM persons WHERE user_id=2 commit transaction
Delete With inner Join:
Delete multiple records with help of join query.
DELETE users, employee, person FROM users INNER JOIN employee INNER JOIN WHERE users.id=employee.user_id AND employee.user_id= person.user_id
Delete with Left Join:
Left join is also one of easiest way to delete all together.
DELETE users, employee, person FROM users LEFT JOIN employee ON users.user_id=employee.id WHERE employee.user_id=person.user_id;
I hope this one help you to delete multiple table data’s.