Shulou(Shulou.com)06/01 Report--

How to delete data from multiple tabular databases by mysql? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

Mysql deletes the data of multiple table databases by establishing a cascade delete relationship between two tables to delete the data of one table while deleting the related data of the other table. The statement is [delete T1 Magi T2 from T2 where condition].

Mysql method to delete data from multiple table databases:

It is impossible to delete multi-table data simply with delete statement, but cascading deletion can be established, and cascade deletion relationship can be established between two tables. It is possible to delete data in one table while deleting related data in another table.

Delete T1 from T2 where conditions

1. Delete all the records whose id values match in the data table T2.

DELETE T1 FROM T1 WHERE t1.id=t2.id or DELETE FROM T1 USING T1 WHERE t1.id=t2.id

2. Find and delete the records that do not match in data table T1 and data table T2.

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.id WHERE t2.id IS NULL

3. Find the same recorded data from two tables and delete the data from both tables

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

Note that delete T1 and T2 in from cannot be aliases.

For example, delete T1 from table_name as T2 from table_name as T1 left join table2_name as T2 on t1.id=t2.id where table_name.id=25 is incorrect in data execution (MYSQL version not less than 5.0 is OK in 5.0)

The above sentence 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)

Thank you for reading! After reading the above, do you have a general idea of how mysql deletes data from multiple tabular databases? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.