Is it possible to delete tables with 10 records in a single statement using a statement like this?

.. DROP TABLE ... WHERE name IN 
 WHERE TABLE_SCHEMA = 'my_db' AND table_rows = 10);
Question author User965748 | Source



First of all, don't trust the table_rows in INFORMATION_SCHEMA.TABLES. It's an estimate, it's not precise. How could it be precise, given that at any given moment, there might be transactions uncommitted that either insert or delete rows? The number of rows in the table may or may not include those changes.

Secondly, the DROP TABLE statement supports only a fixed list of tables to drop. Here's the syntax reference from http://dev.mysql.com/doc/refman/5.7/en/drop-table.html:

DROP [TEMPORARY] TABLE [IF EXISTS]    tbl_name [, tbl_name] ...    [RESTRICT | CASCADE]

This statement does not support a WHERE clause or any conditions. You must name the tables explicitly.

You should develop the habit of reading documentation to find out what syntax is supported. This is the biggest complaint I have about questions on Stack Overflow: too many people skip reading reference documentation. 90% or more of the questions here would be unnecessary if they did.

As @Barmar comments above, you can prepare a dynamic SQL statement with the list of tables you want to drop.

Thirdly, dropping an indeterminate set of tables is very dangerous. You could drop the wrong tables, due to a bug in your code that finds the tables that match the criteria. This is sure to create a disaster of data loss you can't recover from. I never "automate" the dropping of tables.

Answer author Bill-karwin

Ask about this question here!