Digital Paint Discussion Board

Digital Paint Community => Other Stuff => Topic started by: jitspoe on July 03, 2007, 02:11:42 AM

Title: MySQL: Drop column only if it exists
Post by: jitspoe on July 03, 2007, 02:11:42 AM

ALTER TABLE table_name
DROP COLUMN column_name;

If column_name doesn't exist, it barfs and quits.  It also does not support "IF EXISTS" for ALTER, so... how do I make it drop the column if it exists, and if not, just go along its merry way?

Title: Re: MySQL: Drop column only if it exists
Post by: Krizdo4 on July 04, 2007, 08:16:07 PM

This might help out: http://www.databasejournal.com/features/mysql/article.php/3554616

Looks like you can setup an error handler for unknown column and just continue when it's encountered.
Might have some side-effects though. Is this for a one time thing?

Title: Re: MySQL: Drop column only if it exists
Post by: lekky on July 04, 2007, 08:32:34 PM

if exists
(select * from syscolumns
  WHERE id=object_id('table_name') AND name='column_name')
   ALTER TABLE table_name DROP COLUMN column_name

or

if exists
(select * from INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME='tablename' AND COLUMN_NAME='columname' )
   ALTER TABLE table_name DROP COLUMN column_name

quick google search, and no idea if they work or not :p