Cross Table Delete with MySQL

Deleting records with MySQL can be done by referencing records in another table by joining them together. This is useful if you need to delete data from one table based on the values in another, or if you want to delete records from one table where there are no associated records in the second table. Note that although the examples in this article show joins between two tables you can join three, four or more tables if required.

Using a join to delete records in MySQL is only possible with version 4.0 or higher. Unfortunately this is not possible using earlier versions of MySQL, ie 3.23 and earlier.

With MySQL you can do a cross table delete in one of two ways. The first is to use commas with an implicit inner join like in the example below. In these examples we're using a product and productPrice table where product info is stored in the product table and price information in the productPrice table. Each table has a productId field which is what we'll be joining them on.

DELETE product.*, productPrice.*
FROM product p, productPrice pp
WHERE p.productId = pp.productId
AND p.created < '2004-01-01'

The second way is to use "inner join" syntax as in the example below. This is my own personal preference for how to join tables together as it keeps the join conditions with the join statement instead of burying it in the where clause.

DELETE product.*, productPrice.*
FROM product p
INNER JOIN productPrice pp
ON p.productId = pp.productId
WHERE p.created < '2004-01-01'

Note that you don't necessarily need to delete all records from all tables in the query. The example above could just delete from the productPrice table by changing the first line to delete product.*.

You can also use a left join to delete records using MySQL. An example of this is using our product and productPrice tables below, where we are deleting all the records from the product table where there is not an associated record in the productPrice table.

DELETE product.*
FROM product p
LEFT JOIN productPrice pp
ON p.productId = pp.productId
WHERE pp.productId is null

When testing these queries on a test database to make sure they actually executed I discovered it appears you cannot use the alias names for the data to delete (eg delete p.*) otherwise you get the error error 1066 not unique table/alias. To fix this error type in the full table name instead of the alias.

Note there is another article about how to update records with MySQL using a cross join which uses similar examples to update instead of delete.