Cross Table Delete with MySQL

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

THIS IS POSSIBLE


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.

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′

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.

Source Document is here