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:
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:
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:
Deleting across tables is useful, so use it when you need it. Welcome to another introductory article on cross-table updates
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:
DELETE p.*
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