Delete multiple table data in MySQL

Delete multiple table data in MySQL

Delete deletes multiple table data, how can you delete data from multiple associated tables at the same time? Here's an in-depth explanation:

1. Delete from T1 where condition

2. Delete t1 from T1 where condition

3. Delete T1 from T1,t2 where condition

4. Delete t1,t2 from t1,t2 where condition

The first 3 are feasible, and the 4th is not.

That is, you cannot do multiple table delete data operations with the DELETE statement, but you can establish a cascade delete to establish a cascade delete relationship between two tables, you can implement deleting data from one table while deleting the related data in the other table.

1, from the data table t1 those ID values in the data table T2 matching records are all deleted

Delete T1 from t1,t2 where t1.id=t2.id or delete from T1 USING t1,t2 where T1.id=t2.id

2, from the data table T1 in the data table T2 no matching records to find out and delete

Delete T1 from T1 left join T2 on t1.id=t2.id WHERE t2.id is NULL or DELETE from t1,using T1 left join T2 on t1.id=t2.i D WHERE T2.id is NULL

3. Find the same record data from the two tables and delete the data from the two tables

DELETE t1,t2 from T1 left JOIN T2 on t1.id=t2.id WHERE t1.id=25

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

such as: Delete T1,T2 from table_name as T1 left join Table2_name as T2 on t1.id=t2.id where table_name.id=25 execution in data is wrong (MYS QL version not less than 5.0 in 5.0 is possible)

The above statement is rewritten as

Delete table_name,table2_name from table_name as T1 left joins Table2_name as T2 on T1.id=t2.id where table_name.id=25 Execution in the data is wrong (MYSQL version is less than 5.0 in 5.0 is possible)