How to delete multiple table database data in mysql

Mysql method to delete data from multiple tables: first establish a cascade delete relationship between the two tables; then delete the data of one table through the [delete t1, t2 from t1, t2 where condition] statement , just delete related data in another table at the same time.

【Related learning recommendations: mysql tutorial(Video)】

Mysql method of deleting data from multiple tables:

Simple use of the delete statement cannot perform multi-table deletion of data, but cascade deletion can be established. By establishing a cascade delete relationship between two tables, you can delete the data in one table and delete the related data in the other table at the same time.

delete t1,t2 from t1,t2 where 条件

1. Delete all records from data table t1 that have matching ID values ​​in data table t2.

DELETE t1 FROM t1,t2 WHERE t1.id=t2.id 或 DELETE FROM t1 USING t1,t2 WHERE t1.id=t2.id

2. From data table t1, delete all records in data table t2. If there are no matching records in t2, find and delete them

DELETE t1 FROM t1 LEFT JOIN T2 ON t1.id=t2.id WHERE t2.id IS NULL 或 DELETE FROM t1,USING t1 LEFT JOIN T2 ON t1.id=t2.id WHERE t2.id IS NULL

3. Find the data of the same records from the two tables and delete the data in both tables

DELETE t1,t2 from t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t1.id=25

Note The t1,t2 in delete t1,t2 from here cannot be an alias

. For example: delete t1,t2 from table_name as t1 left join table2_name as t2 on t1.id=t2.id where table_name.id=25 It is wrong to execute in the data (MYSQL version is not less than 5.0, it is ok in 5.0)

The above statement is rewritten as

delete table_name,table2_name from table_name as t1 left join table2_name as t2 on t1.id=t2.id where table_name.id=25

It is wrong to execute in the data (MYSQL version less than 5.0 is ok in 5.0)

If you want to learn more about programming, please pay attention php training column!

The above is the detailed content of How to delete multiple table database data in mysql. For more information, please follow other related articles on the PHP Chinese website!