CODEWITHSUNDEEP
mysqlsqlsql-delete
alwbtc
alwbtc

Reputation: 29495

Deleting a row if a column exists in MySQL

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?

Upvotes: 0

Views: 951

Answers (2)

Alberto Moro
Alberto Moro

Reputation: 1013

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;

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32021

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

Upvotes: 1

Related Questions