[mysql] Using ALTER to drop a column if it exists in MySQL

How can ALTER be used to drop a column in a MySQL table if that column exists?

I know I can use ALTER TABLE my_table DROP COLUMN my_column, but that will throw an error if my_column does not exist. Is there alternative syntax for dropping the column conditionally?

I'm using MySQL version 4.0.18.

You can use this script, use your column, schema and table name

 IF EXISTS (SELECT *
                         FROM INFORMATION_SCHEMA.COLUMNS
                         WHERE TABLE_NAME = 'TableName' AND COLUMN_NAME = 'ColumnName' 
                                             AND TABLE_SCHEMA = SchemaName)
    BEGIN
       ALTER TABLE TableName DROP COLUMN ColumnName;
    END;

Chase Seibert's answer works, but I'd add that if you have several schemata you want to alter the SELECT thus:

select * from information_schema.columns where table_schema in (select schema()) and table_name=...

Perhaps the simplest way to solve this (that will work) is:

• CREATE new_table AS SELECT id, col1, col2, ... (only the columns you actually want in the final table) FROM my_table;

• RENAME my_table TO old_table, new_table TO my_table;

• DROP old_table;

Or keep old_table for a rollback if needed.

This will work but foreign keys will not be moved. You would have to re-add them to my_table later; also foreign keys in other tables that reference my_table will have to be fixed (pointed to the new my_table).

Good Luck...

I know this is an old thread, but there is a simple way to handle this requirement without using stored procedures. This may help someone.

set @exist_Check := (
    select count(*) from information_schema.columns 
    where TABLE_NAME='YOUR_TABLE' 
    and COLUMN_NAME='YOUR_COLUMN' 
    and TABLE_SCHEMA=database()
) ;
set @sqlstmt := if(@exist_Check>0,'alter table YOUR_TABLE drop column YOUR_COLUMN', 'select ''''') ;
prepare stmt from @sqlstmt ;
execute stmt ;

Hope this helps someone, as it did me (after a lot of trial and error).

Perhaps the simplest way to solve this (that will work) is:

• CREATE new_table AS SELECT id, col1, col2, ... (only the columns you actually want in the final table) FROM my_table;

• RENAME my_table TO old_table, new_table TO my_table;

• DROP old_table;

Or keep old_table for a rollback if needed.

This will work but foreign keys will not be moved. You would have to re-add them to my_table later; also foreign keys in other tables that reference my_table will have to be fixed (pointed to the new my_table).

Good Luck...

There is no language level support for this in MySQL. Here is a work-around involving MySQL information_schema meta-data in 5.0+, but it won't address your issue in 4.0.18.

drop procedure if exists schema_change;

delimiter ';;'
create procedure schema_change() begin

    /* delete columns if they exist */
    if exists (select * from information_schema.columns where table_schema = schema() and table_name = 'table1' and column_name = 'column1') then
        alter table table1 drop column `column1`;
    end if;
    if exists (select * from information_schema.columns where table_schema = schema() and table_name = 'table1' and column_name = 'column2') then
        alter table table1 drop column `column2`;
    end if;

    /* add columns */
    alter table table1 add column `column1` varchar(255) NULL;
    alter table table1 add column `column2` varchar(255) NULL;

end;;

delimiter ';'
call schema_change();

drop procedure if exists schema_change;

I wrote some more detailed information in a blog post.

Chase Seibert's answer works, but I'd add that if you have several schemata you want to alter the SELECT thus:

select * from information_schema.columns where table_schema in (select schema()) and table_name=...

I just built a reusable procedure that can help making DROP COLUMN idempotent:

-- column_exists:

DROP FUNCTION IF EXISTS column_exists;

DELIMITER $$
CREATE FUNCTION column_exists(
  tname VARCHAR(64),
  cname VARCHAR(64)
)
  RETURNS BOOLEAN
  READS SQL DATA
  BEGIN
    RETURN 0 < (SELECT COUNT(*)
                FROM `INFORMATION_SCHEMA`.`COLUMNS`
                WHERE `TABLE_SCHEMA` = SCHEMA()
                      AND `TABLE_NAME` = tname
                      AND `COLUMN_NAME` = cname);
  END $$
DELIMITER ;

-- drop_column_if_exists:

DROP PROCEDURE IF EXISTS drop_column_if_exists;

DELIMITER $$
CREATE PROCEDURE drop_column_if_exists(
  tname VARCHAR(64),
  cname VARCHAR(64)
)
  BEGIN
    IF column_exists(tname, cname)
    THEN
      SET @drop_column_if_exists = CONCAT('ALTER TABLE `', tname, '` DROP COLUMN `', cname, '`');
      PREPARE drop_query FROM @drop_column_if_exists;
      EXECUTE drop_query;
    END IF;
  END $$
DELIMITER ;

Usage:

CALL drop_column_if_exists('my_table', 'my_column');

Example:

SELECT column_exists('my_table', 'my_column');       -- 1
CALL drop_column_if_exists('my_table', 'my_column'); -- success
SELECT column_exists('my_table', 'my_column');       -- 0
CALL drop_column_if_exists('my_table', 'my_column'); -- success
SELECT column_exists('my_table', 'my_column');       -- 0

There is no language level support for this in MySQL. Here is a work-around involving MySQL information_schema meta-data in 5.0+, but it won't address your issue in 4.0.18.

drop procedure if exists schema_change;

delimiter ';;'
create procedure schema_change() begin

    /* delete columns if they exist */
    if exists (select * from information_schema.columns where table_schema = schema() and table_name = 'table1' and column_name = 'column1') then
        alter table table1 drop column `column1`;
    end if;
    if exists (select * from information_schema.columns where table_schema = schema() and table_name = 'table1' and column_name = 'column2') then
        alter table table1 drop column `column2`;
    end if;

    /* add columns */
    alter table table1 add column `column1` varchar(255) NULL;
    alter table table1 add column `column2` varchar(255) NULL;

end;;

delimiter ';'
call schema_change();

drop procedure if exists schema_change;

I wrote some more detailed information in a blog post.

You can use this script, use your column, schema and table name

 IF EXISTS (SELECT *
                         FROM INFORMATION_SCHEMA.COLUMNS
                         WHERE TABLE_NAME = 'TableName' AND COLUMN_NAME = 'ColumnName' 
                                             AND TABLE_SCHEMA = SchemaName)
    BEGIN
       ALTER TABLE TableName DROP COLUMN ColumnName;
    END;

I realise this thread is quite old now, but I was having the same problem. This was my very basic solution using the MySQL Workbench, but it worked fine...

1. get a new sql editor and execute SHOW TABLES to get a list of your tables
2. select all of the rows, and choose copy to clipboard (unquoted) from the context menu
3. paste the list of names into another editor tab
4. write your query, ie ALTER TABLE x DROP a;
5. do some copying and pasting, so you end up with separate query for each table
6. Toggle whether the workbench should stop when an error occurs
7. Hit execute and look through the output log

any tables which had the table now haven't any tables which didn't will have shown an error in the logs

then you can find/replace 'drop a' change it to 'ADD COLUMN b INT NULL' etc and run the whole thing again....

a bit clunky, but at last you get the end result and you can control/monitor the whole process and remember to save you sql scripts in case you need them again.

I know this is an old thread, but there is a simple way to handle this requirement without using stored procedures. This may help someone.

set @exist_Check := (
    select count(*) from information_schema.columns 
    where TABLE_NAME='YOUR_TABLE' 
    and COLUMN_NAME='YOUR_COLUMN' 
    and TABLE_SCHEMA=database()
) ;
set @sqlstmt := if(@exist_Check>0,'alter table YOUR_TABLE drop column YOUR_COLUMN', 'select ''''') ;
prepare stmt from @sqlstmt ;
execute stmt ;

Hope this helps someone, as it did me (after a lot of trial and error).

I realise this thread is quite old now, but I was having the same problem. This was my very basic solution using the MySQL Workbench, but it worked fine...

1. get a new sql editor and execute SHOW TABLES to get a list of your tables
2. select all of the rows, and choose copy to clipboard (unquoted) from the context menu
3. paste the list of names into another editor tab
4. write your query, ie ALTER TABLE x DROP a;
5. do some copying and pasting, so you end up with separate query for each table
6. Toggle whether the workbench should stop when an error occurs
7. Hit execute and look through the output log

any tables which had the table now haven't any tables which didn't will have shown an error in the logs

then you can find/replace 'drop a' change it to 'ADD COLUMN b INT NULL' etc and run the whole thing again....

a bit clunky, but at last you get the end result and you can control/monitor the whole process and remember to save you sql scripts in case you need them again.

I just built a reusable procedure that can help making DROP COLUMN idempotent:

-- column_exists:

DROP FUNCTION IF EXISTS column_exists;

DELIMITER $$
CREATE FUNCTION column_exists(
  tname VARCHAR(64),
  cname VARCHAR(64)
)
  RETURNS BOOLEAN
  READS SQL DATA
  BEGIN
    RETURN 0 < (SELECT COUNT(*)
                FROM `INFORMATION_SCHEMA`.`COLUMNS`
                WHERE `TABLE_SCHEMA` = SCHEMA()
                      AND `TABLE_NAME` = tname
                      AND `COLUMN_NAME` = cname);
  END $$
DELIMITER ;

-- drop_column_if_exists:

DROP PROCEDURE IF EXISTS drop_column_if_exists;

DELIMITER $$
CREATE PROCEDURE drop_column_if_exists(
  tname VARCHAR(64),
  cname VARCHAR(64)
)
  BEGIN
    IF column_exists(tname, cname)
    THEN
      SET @drop_column_if_exists = CONCAT('ALTER TABLE `', tname, '` DROP COLUMN `', cname, '`');
      PREPARE drop_query FROM @drop_column_if_exists;
      EXECUTE drop_query;
    END IF;
  END $$
DELIMITER ;

Usage:

CALL drop_column_if_exists('my_table', 'my_column');

Example:

SELECT column_exists('my_table', 'my_column');       -- 1
CALL drop_column_if_exists('my_table', 'my_column'); -- success
SELECT column_exists('my_table', 'my_column');       -- 0
CALL drop_column_if_exists('my_table', 'my_column'); -- success
SELECT column_exists('my_table', 'my_column');       -- 0