Deleting a row if a column exists in MySQL

Question

I am trying to delete rows if new_id column value is equal to 2 from multiple mysql tables, but I don't know if all those tables have column new_id.

I try the following statement, but it gives a syntax error:

DELETE FROM table_name WHERE new_id =2 IF EXISTS new_id int(11)

How to do this?

You can get all tables with that column name using [this](https://stackoverflow.com/questions/193780/how-to-find-all-the-tables-in-mysql-with-specific-column-names-in-them) and then create dynamic query to delete the data from the resultset of that query. — Mahesh, Jul 02 '19 at 06:52

score 1 · Accepted Answer · answered Jul 02 '19 at 06:50

1

you can get column name by using below query

SHOW COLUMNS FROM `table_name` LIKE 'new_id';

Then from frontend you can take the decision to execute delete query

answered Jul 02 '19 at 06:50

Zaynul Abadin Tuhin

31,407
5
33
63

score 1 · Answer 2 · answered Jul 02 '19 at 06:54

You can check in information schema:

IF EXISTS ( SELECT * 
            FROM information_schema.columns 
            WHERE table_name = 'table_name' 
            AND column_name = 'new_id' 
            AND table_schema = DATABASE () ) THEN
DELETE FROM table_name WHERE new_id = 2;
END IF;

Deleting a row if a column exists in MySQL

2 Answers2