CODEWITHSUNDEEP
mysqlmysql-error-1064
Mokus
Mokus

Reputation: 10410

Add column if not exist

How could I want to add a column if not exist. I tried this code:

IF EXISTS( SELECT NULL
            FROM INFORMATION_SCHEMA.COLUMNS
           WHERE table_name = 'forwind.measuringdata_fino_10_00000000'
             AND table_schema = 'forwind'
             AND column_name != 'F1_USA(40)_u')  THEN

  ALTER TABLE `forwind.measuringdata_fino_10_00000000` ADD `F1_USA(40)_u` FLOAT NOT NULL default '0';

END IF;

but I get the following error:

Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS( SELECT NULL FROM INFORMATION_SCHEMA.COLUMNS

Good for me if somebody know an other solution!

Upvotes: 1

Views: 7879

Answers (2)

vivek sharma
vivek sharma

Reputation: 9

------Add an IpAddress column in ActionsTracking table if -----

DECLARE @tableName varchar(MAX) ='ActionsTracking'
DECLARE @columnName varchar(MAX) ='IpAddress'

IF EXISTS(SELECT 1 FROM sys.columns
         WHERE Name = @columnName
         AND Object_ID = Object_ID(@tableName))
   PRINT 'Column Already Exists'
ELSE
BEGIN
EXEC('ALTER TABLE '+@tableName+'
       ADD '+ @columnName +' varchar(15) null')
   PRINT 'Column Added Sucessfully'
END

Upvotes: 0

Ike Walker
Ike Walker

Reputation: 65587

MySQL does not support anonymous IF statements like that.

You have at least 3 options to address this:

1) Just run the ALTER TABLE statement and ignore the Duplicate column name error if the column already exists. If you're executing a bunch of DDL in a script you can use mysql -f to ignore the errors and keep going. 2) Use a scripting language such as bash, python, perl etc to check for the existence of the column and then add it if it does not already exist. 3) Create a stored procedure in MySQL to check for the existence of the column and then add it if it does not already exist.

P.S. As an aside, I recommend against putting parentheses in column names, because that forces you to quote the column name every time you reference it.

Upvotes: 7

Related Questions