SQL add new columns ignoring duplicates

mysql, sql, webmin

Solution

The `if ... begin ... end` is SQL Server syntax. For MySQL, it's more like `if ... then ... end if`:

if not exists (select * from information_schema.columns
    where column_name = 'customer' and table_name = 'orders') then
    alter table orders add customer int(1) null;
end if

In reply to your comment: in MySQL, you can't type compound statements at the command line. They have to be in a function or stored procedure. For example:

drop procedure if exists sp_addcolumn;

delimiter //
create procedure sp_addcolumn()
begin
  if not exists (select * from INFORMATION_SCHEMA.COLUMNS 
      where table_name = 'orders' and column_name = 'customer') then
    alter table `orders` add column `customer` int(1) null;
  end if;
end//

delimiter ;
call sp_addcolumn;

There is an open request on the MySQL bug tracker to allow `if` statements outside stored procedures. It's current status is Needs Triage.

Problem

We have quite a few databases, and we're trying to run upgrade scripts on all of them to bring them up to date - as such, they all have different columns and tables. We want to add new tables and columns if they arent already present, so for instance ``` CREATE TABLE IF NOT EXISTS `orders` ( `id` INT (11) NOT NULL , `value` VARCHAR (50) , `designId` INT (11) , PRIMARY KEY ( `id`)); ``` That works, but we're looking for the same kind of solution for columns. Our current solution throws Error Code: 1060 - Duplicate column name. ``` ALTER TABLE `orders` ADD COLUMN `customer` INT (1) NULL; ``` I've tried the following from garry passarella, but i get an error claiming incorrect sql syntax: ``` IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'orders' AND COLUMN_NAME = 'customer') BEGIN ALTER TABLE orders ADD customer BIT DEFAULT NULL END ``` If there is something we can use to get each line to ignore duplicates, or get the entire script to ignore error code 1060, it would be much appreciated.

Original source