Cross deleting records from multiple tables in mysql
I’ve been asked many times how to do a MySQL join delete, that is delete records in multiple tables joined in MySQL. Here are three of methods that should get anyone started.
As always, create a backup before updating or deleting records.
Simple Inner Join Delete Method
The simple inner join method uses commas with an implicit inner join like in the example below. Here we’re using an items and price table, where each table has an itemId field which is what we join them on.
DELETE i.*, p.* FROM items i, price p WHERE i.itemId = p.itemId AND i.itemId = 101
Traditional Inner Join Delete Method
In the traditional inner join we’re keeping the join statement clear of the where clause, but it works as well. Whatever personal preference you may have. Note that you can delete individual table records from one table only, if you require to do so.
DELETE i.*, p.* FROM items i INNER JOIN price p ON i.itemId = p.itemId WHERE i.itemId = 101
Delete records that are missing in the second table
Using a left join delte, we can also delete records that have no corresponding record in the joined table. The following query accomplishes that. In the example below, we delete all records in the items table that have no record in the price table.
DELETE i.* FROM items i LEFT JOIN price p ON i.itemId = p.itemId WHERE p.itemId IS NULL
Know of some nifty SQL queries that accomplish above tasks in a different way? Let us know!