how to drop a column if exists in mysql?

Question

ALTER TABLE user DROP COLUMN registered_date

We can drop a column using the above command. But how can i drop it only if the column registered_date exist?

It doesn't look like there is a way, but worst case scenario you would get an error which you could handle in your app layer. — Tim Biegeleisen, Sep 28 '16 at 05:21
it will look like this ALTER TABLE user DROP COLUMN IF EXISTS registered_date; — Deepak Joshi, Sep 28 '16 at 05:23
ok please please read the link share by @Tim Biegeleisen in first comment. this one http://stackoverflow.com/questions/173814/mysql-alter-syntax-to-drop-a-column-if-it-exists — Deepak Joshi, Sep 28 '16 at 05:30

Jibin Balachandran · Answer 1 · 2016-09-28T05:27:59.257

-2

IF EXISTS(SELECT * 
          FROM information_schema.COLUMNS 
          WHERE 
          TABLE_SCHEMA = 'db_name' 
          AND TABLE_NAME = 'table_name' 
          AND COLUMN_NAME = 'column_name')
BEGIN
 ALTER TABLE table_name DROP COLUMN column_name
END

OR

IF (SELECT COUNT(*) 
          FROM information_schema.COLUMNS 
          WHERE 
          TABLE_SCHEMA = 'db_name' 
          AND TABLE_NAME = 'table_name' 
          AND COLUMN_NAME = 'column_name'>0)
BEGIN
 ALTER TABLE table_name DROP COLUMN column_name
END

edited Sep 28 '16 at 05:27

answered Sep 28 '16 at 05:22

Jibin Balachandran

3,271
1
21
38

i get a syntax error 'if is not valid input at this position'. if i follow above – CloudSeph Sep 28 '16 at 05:24
@IvanCloud those statement combos are only available in like a stored proc – Drew Sep 28 '16 at 05:27
doesn't work invalid syntax near if – Clintm Apr 01 '17 at 00:26

how to drop a column if exists in mysql?

1 Answers1