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!

Listed in MySQL

Tags: join delete, multiple delete, MySQL