What is the syntax to drop a column in a MySQL table, if that column exists on version 4.0.18?
+6
A:
There is none: MySQL Feature Request. Simply check for existence first in the client or catch the error.
MattW.
2008-10-06 10:31:22
Is there a way to do it in pure SQL?
Tom
2010-01-19 08:56:08
+4
A:
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_name = 'table1' and column_name = 'column1') then
alter table table1 drop column `column1`;
end if;
if exists (select * from information_schema.columns where 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
2010-01-22 20:57:03
+1
A:
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=...
DrHyde
2010-01-28 16:03:50