how to drop column from table if exists

Question

This is the query i am using:

IF  EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME =( N'CustomerVariable1Value'))
begin Alter table temp.DIM_BE_ACCOUNT  drop column CustomerVariable1Value

It works fine the first time but when I run it again, it shows error. How to make it error free and executes it many number of times?

Error message:

ALTER TABLE DROP COLUMN failed because column 'CustomerVariable1Value' does not exist in table 'DIM_BE_ACCOUNT'.

"this is the query i am using: it works fine for first time but when i runs again it shows error " - that is entirely expected. First run deletes the column. Hence ->> " failed because column 'CustomerVariable1Value' does not exist in table 'DIM_BE_ACCOUNT'." — Mitch Wheat, May 18 '15 at 16:05
possible duplicate of [MySQL Alter syntax to drop a column if it exists](http://stackoverflow.com/questions/173814/mysql-alter-syntax-to-drop-a-column-if-it-exists) — Eric Hotinger, May 18 '15 at 16:05

score 2 · Accepted Answer · answered May 18 '15 at 16:36

You are only looking for a column name out of all column names in the entire MySQL instance. You need to also filter by schema (=database) and table names:

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = N'CustomerVariable1Value' AND TABLE_NAME = 'MyTableName' AND TABLE_SCHEMA = 'MyDatabase')

score 0 · Answer 2 · answered Aug 07 '17 at 13:15

Here is a solution that does not involve querying INFORMATION_SCHEMA, it simply ignores the error if the column does not exist.

DROP PROCEDURE IF EXISTS `?`;
DELIMITER //
CREATE PROCEDURE `?`
(
)
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
  ALTER TABLE `table_name` DROP COLUMN `column_name`;
END //
DELIMITER ;
CALL `?`();
DROP PROCEDURE `?`;

P.S. Feel free to give it other name rather than ?

how to drop column from table if exists

2 Answers2

Linked