CODEWITHSUNDEEP
mysqlsqlstored-procedures
Binny
Binny

Reputation: 101

how to drop column from table if exists

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

Upvotes: 2

Views: 5486

Answers (2)

raugfer
raugfer

Reputation: 1904

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 ?

Upvotes: 0

siride
siride

Reputation: 209625

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

Upvotes: 2

Related Questions