MySQL deletes multiple tables and multiple table records SQL statements

Bulk Delete Multiple tables

Delete all pre_ prefixes table

The code is as follows	Copy Code
SELECT CONCAT (' drop table ', TABLE_NAME, '; ') from Information_schema.tables where Information_schema.tables.TABLE_NAME like ' pre_% ';

Deletes all tables with pre_ prefixes and does not delete the PRE_UC prefix table

The code is as follows	Copy Code
SELECT CONCAT (' drop table ', TABLE_NAME, '; ') from Information_schema.tables WHERE Information_schema.tables.TABLE_NAME like ' pre_% ' and Information_schema.tables.TABLE_NAME don't like

' pre_uc% '; Copy the resulting results and then execute them again.

Delete multiple tables of the same data

MySQL database, if you need more than one table to delete data, what should be done? The following will introduce you to the MySQL multiple table deletion method, I hope to enlighten you.

1, from the data table t1 those ID values in the datasheet t2 have matching records are deleted

The code is as follows	Copy Code
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 datasheet T1 in the data table T2 no matching records to find out and delete

The code is as follows	Copy Code
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 in T1.id=t2.id WHERE t2.id is NULL

3, from the two tables to find the same record of data and two of the data in the table deleted

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

The code is as follows	Copy Code
Delete T1,T2 from table_name as T1 left join Table2_name as T2 on T1.id=t2.id where table_name.id=25

Executing in the data is wrong (MYSQL version is not less than 5.0 in 5.0 is OK)

The above statement is rewritten as

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

Executing in the data is wrong (MYSQL version is less than 5.0 in 5.0 is OK)

Delete extra duplicate records in a table, leaving only the smallest rowID records (single field)

The code is as follows	Copy Code
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 extra duplicate records in a table, leaving only the smallest rowID records (multiple fields)

The code is as follows	Copy Code
Delete from Table A 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 duplicate records (single field, multiple fields)

The code is as follows	Copy Code
Delete from table where ID isn't in (select min (id) from table group by name) Or Delete from table where ID isn't in (select min (id) from table Group By field 1, Field 2)

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

code is as follows	copy code
delete from Table where ID in (SELECT MAX (ID) from table group by name has COUNT (*) >1)