A few days ago, I wrote a summary of Mysql cross-Table update. Today, let's take a 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:
Copy codeThe Code is as follows:
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:
Copy codeThe Code is as follows:
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:
Copy codeThe Code is as follows:
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.