CODEWITHSUNDEEP
mysqlsql
Evgenij Reznik
Evgenij Reznik

Reputation: 18594

Delete rows from all tables

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';

Upvotes: 6

Views: 4687

Answers (2)

1000111
1000111

Reputation: 13519

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.

Upvotes: 9

Ezequiel
Ezequiel

Reputation: 13

Create a cursor to iterate over varieble. 1. You can select a list of tables SHOW tables ----> To variable 2. Create a cursor http://www.mysqltutorial.org/mysql-cursor/ 3. Run cursor with the code that you want!

Best regards!

Upvotes: 0

Related Questions