Mysql supports delete multiple table records across tables _ MySQL-mysql tutorial

Mysql supports cross-table delete multi-table record bitsCN.com a few days ago, I wrote a summary of Mysql cross-table update. today we will look at cross-table deletion.
After Mysql4.0, mysql began to support cross-table delete.
Mysql can delete multiple table records in an SQL statement at the same time, or delete records in a table based on the relationship between multiple tables.
Suppose we have two tables: the Product table and the ProductPrice table. The former has the basic information about the Product, and the latter has the price of the Product.
The first cross-table deletion method is to delete multiple tables without the need for join. during The delete operation, multiple tables are separated by commas (,). The following SQL statement is used:

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

The second cross-table deletion method is to use inner join to specify the association between the two tables in join. the following SQL statement is used:

DELETE p. *, pp .*
FROM product p
Inner join productPrice pp
ON p. productId = pp. productId
WHERE p. created <'2017-01-01'

Note:The preceding SQL statement table deletes data from both the Product and ProductPrice tables. However, you can specify DELETE product. * only records in the product table are deleted, but records in the ProductPrice table are not processed.

You can also use left join for cross-table deletion. for example, we want to delete all the Product table records that are not recorded in the ProductPrice table. The following SQL statement:

DELETE p .*
FROM product p
Left join productPrice pp
ON p. productId = pp. productId
WHERE pp. productId is null

Cross-table deletion is useful. use it as needed. Read another article about cross-table update, bitsCN.com.