mysql supports deleting multiple table records across table delete

  • 2020-05-14 05:12:33
  • OfStack

A few days ago I wrote a summary of Mysql cross table update, today we will look at cross table delete.
After Mysql4.0, mysql started supporting delete across tables.
Mysql can delete multiple table records in a single sql statement, or delete records in a single table based on the relationship between multiple tables.
Let's say we have two tables: Product and ProductPrice. The former has the basic information of Product, while the latter has the price of Product.
The first way to delete across tables is to specify the use of a half-angle comma to separate multiple tables at delete instead of join. The sql statement is as follows:
DELETE p.*, pp.* 
FROM product p, productPrice pp 
WHERE p.productId = pp.productId 
AND p.created < '2004-01-01' 

The second way to delete across tables is to specify the association between two tables in join using inner join, as shown in the sql statement below:
DELETE p.*, pp.* 
FROM product p 
INNER JOIN productPrice pp 
ON p.productId = pp.productId 
WHERE p.created < '2004-01-01' 

Note: cross-table deletes do not have to delete data from all tables. The sql statement table above deletes data from both Product and ProductPrice tables, but you can specify DELETE product.* to delete only records from product tables and not process records from ProductPrice tables.

left join can also be used for cross-table deletion, for example we want to delete all Product table records that have no records in the ProductPrice table. The following sql statement:
FROM product p 
LEFT JOIN productPrice pp 
ON p.productId = pp.productId 
WHERE pp.productId is null 

Deleting across tables is useful, so use it when you need it. Welcome to another introductory article on cross-table updates

Related articles: