A summary of the MySQL cross-table update was written a few days ago, and today we look at the cross-table deletions.
After Mysql4.0, MySQL began to support cross-table Delete.
MySQL can delete multiple table records in one SQL statement at the same time, or you can delete records from a table based on relationships between multiple tables.
Suppose we have two tables: the Product table and the Productprice table. The former has basic product information and the latter has the product price.
The first type of cross-table deletion is to delete without a join, specifying a comma-delimited number of tables for deletion with the following SQL statement:
Copy CodeThe code 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 cross-table deletes is to use the inner join to specify an association between the two tables in the join, as in the following SQL statement:
Copy CodeThe code is as follows:
DELETE p.*, pp.*
From Product P
INNER JOIN Productprice pp
On p.productid = Pp.productid
WHERE p.created < ' 2004-01-01 '
Note:Cross-table deletion eliminates the need to delete data from all tables, and the SQL statement table above deletes the data from both product and productprice two tables, but you can specify delete product.* to delete only the records in the Product table. The records in the Productprice table are not processed.
Cross-table deletion can also use a LEFT join, for example we want to delete all 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, so use it when you need it. Read another article about cross-table updates that you might be interested in:
- MySQL deleted data Delete vs. truncate statement usage comparison
- A summary of MySQL delete issues
- MySQL Delete syntax uses detailed parsing
MySQL supports cross-table Delete to delete multiple table records
