Delete rows from all tables

Question

I have a database with more than 100 tables. About 20 of them have a specific column, say column1.

Can I delete rows in all tables, where column1="abc", without specifying each table?

So I need something like this:

DELETE FROM [all tables] WHERE column1 = 'abc';

Answer 1

The easiest way may be the following:

SELECT 
CONCAT('DELETE FROM ',TABLE_NAME," WHERE column1 = 'abc';") comd
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'YOUR_DATABASE_NAME'
AND COLUMN_NAME ='column1';

This query will give you output like below:

DELETE FROM TABLE_1 WHERE column1 = 'abc';
DELETE FROM TABLE_2 WHERE column1 = 'abc';
DELETE FROM TABLE_3 WHERE column1 = 'abc';
DELETE FROM TABLE_4 WHERE column1 = 'abc';
.
.
.

Now copy these DELETE commands and execute all.

---

Note: In other way, you can write a stored program where you can turn these generated command strings into executable command/query through prepare statement.

But you can prefer the easiest way I've suggested above in order to bypass complexity.