Mysql deletes SQL statements for multiple tables and multiple table records

This article summarizes how to delete the relational data between multiple data tables and multiple data tables at the same time. For more information, see.

This article summarizes how to delete the relational data between multiple data tables and multiple data tables at the same time. For more information, see.

Batch Delete multiple tables

Delete all pre _ prefix tables

The Code is as follows:
Select concat ('drop table', table_name, ';') FROM information_schema.tables where Information_schema.tables.TABLE_NAME LIKE 'pre _ % ';

Delete all pre _ prefix tables and do not delete pre_uc prefix tables

The Code is as follows:
Select concat ('drop table', table_name, ';') FROM information_schema.tables WHERE Information_schema.tables.TABLE_NAME LIKE 'pre _ % 'AND information_schema.tables.TABLE_NAME NOT LIKE

'Pre _ uc % '; copy the result and execute it again.

Delete data from multiple tables

In the MySQL database, what should I do if I need to delete data from multiple tables at the same time? The following describes how to delete multiple tables in MySQL.

1. Delete all matching records with id values in Table T2.

The Code is as follows:
DELETE t1 FROM t1, t2 WHERE t1.id = t2.id or delete from t1 USING t1, t2 WHERE t1.id = t2.id

2. Find and delete no matching records in data table T2.

The Code is as follows:
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 data with the same record from the two tables and delete the data from both tables.

The Code is as follows:
DELETE t1, t2 from t1 left join t2 ON t1.id = t2.id WHERE t1.id = 25

Note that delete t1, t1 in t2 from, and t2 cannot be aliases.

For example:

The Code is as follows:
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 the data is incorrect (MYSQL version is not less than 5.0 is acceptable in 5.0)

The preceding statement is rewritten

The Code is as follows:
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

Execution in the data is incorrect (MYSQL version earlier than 5.0 is acceptable in 5.0)

Delete unnecessary duplicate records in the table, leaving only the records with the smallest rowid (single field)

The Code is as follows:
Delete From table Where Field 1 In (Select Field 1 From Table Group By field 1 Having Count (Field 1)> 1) And Rowid Not In (Select Min (Rowid) From Table Group By field 1 Having Count (Field 1)> 1)

Delete unnecessary duplicate records in the table, leaving only the records with the smallest rowid (multiple fields)

The Code is as follows:
Delete From Table Where (a. Field 1, a. Field 2) In (Select Field 1, Field 2 From Table Group By field 1, Field 2 Having Count (*)> 1) And Rowid Not In (Select Min (Rowid) From Table Group By field 1, Field 2 Having Count (*)> 1)

5. Delete more Repeated Records (single field, multiple fields)

The Code is as follows:
Delete from table where id not in (select min (id) from table group by name) Or Delete from table where id not in (select min (id) from table group by field 1, Field 2)

6. Delete redundant duplicate records (single field, multiple fields)

The Code is as follows:
Delete from table where id in (select max (id) from table group by name having count (*)> 1)