Using ALTER to drop a column if it exists in MySQL

Question

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

User · Answer

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

User · Answer

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

User · Answer

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  lt   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

User · Answer

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  lt   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

User · Answer

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 gt 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

User · Answer

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      get a new sql editor and execute SHOW TABLES to get a list of your tables select all of the rows  and choose copy to clipboard  unquoted  from the context menu paste the list of names into another editor tab write your query  ie ALTER TABLE x DROP a  do some copying and pasting  so you end up with separate query for each table Toggle whether the workbench should stop when an error occurs 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

User · Answer

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

User · Answer

For MySQL  there is none  MySQL Feature Request    Allowing this is arguably a really bad idea  anyway  IF EXISTS indicates that you re running destructive operations on a database with  to you  unknown structure  There may be situations where this is acceptable for quick-and-dirty local work  but if you re tempted to run such a statement against production data  in a migration etc    you re playing with fire   But if you insist  it s not difficult to simply check for existence first in the client  or to catch the error   MariaDB also supports the following starting with 10 0 2   DROP  COLUMN   IF EXISTS  col name    i  e   ALTER TABLE my table DROP IF EXISTS my column    But it s arguably a bad idea to rely on a non-standard feature supported by only one of several forks of MySQL

User · Answer

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      get a new sql editor and execute SHOW TABLES to get a list of your tables select all of the rows  and choose copy to clipboard  unquoted  from the context menu paste the list of names into another editor tab write your query  ie ALTER TABLE x DROP a  do some copying and pasting  so you end up with separate query for each table Toggle whether the workbench should stop when an error occurs 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

User · Answer

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

User · Answer

For MySQL  there is none  MySQL Feature Request    Allowing this is arguably a really bad idea  anyway  IF EXISTS indicates that you re running destructive operations on a database with  to you  unknown structure  There may be situations where this is acceptable for quick-and-dirty local work  but if you re tempted to run such a statement against production data  in a migration etc    you re playing with fire   But if you insist  it s not difficult to simply check for existence first in the client  or to catch the error   MariaDB also supports the following starting with 10 0 2   DROP  COLUMN   IF EXISTS  col name    i  e   ALTER TABLE my table DROP IF EXISTS my column    But it s arguably a bad idea to rely on a non-standard feature supported by only one of several forks of MySQL

User · Answer

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

User · Answer

For MySQL  there is none  MySQL Feature Request    Allowing this is arguably a really bad idea  anyway  IF EXISTS indicates that you re running destructive operations on a database with  to you  unknown structure  There may be situations where this is acceptable for quick-and-dirty local work  but if you re tempted to run such a statement against production data  in a migration etc    you re playing with fire   But if you insist  it s not difficult to simply check for existence first in the client  or to catch the error   MariaDB also supports the following starting with 10 0 2   DROP  COLUMN   IF EXISTS  col name    i  e   ALTER TABLE my table DROP IF EXISTS my column    But it s arguably a bad idea to rely on a non-standard feature supported by only one of several forks of MySQL

User · Answer

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

User · Answer

For MySQL  there is none  MySQL Feature Request    Allowing this is arguably a really bad idea  anyway  IF EXISTS indicates that you re running destructive operations on a database with  to you  unknown structure  There may be situations where this is acceptable for quick-and-dirty local work  but if you re tempted to run such a statement against production data  in a migration etc    you re playing with fire   But if you insist  it s not difficult to simply check for existence first in the client  or to catch the error   MariaDB also supports the following starting with 10 0 2   DROP  COLUMN   IF EXISTS  col name    i  e   ALTER TABLE my table DROP IF EXISTS my column    But it s arguably a bad idea to rely on a non-standard feature supported by only one of several forks of MySQL

User · Answer

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

User · Answer

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 gt 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

User · Answer

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

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

Examples related to mysql

Examples related to ddl

Examples related to mysql4