Add column if not exist

Question

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!

related http://stackoverflow.com/questions/972922/add-column-to-mysql-table-if-it-does-not-exist — Haim Evgi, Apr 27 '11 at 10:11
You also need to reverse your logic: `NOT EXISTS` and `AND column_name = 'F1_USA(40)_u'` — ypercubeᵀᴹ, Apr 27 '11 at 10:18

score 7 · Accepted Answer · answered Apr 27 '11 at 13:58

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.

score 0 · Answer 2 · answered Sep 23 '20 at 12:05

0

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

answered Sep 23 '20 at 12:05

vivek sharma

9
1

1

While this code may answer the question, providing additional context regarding how and/or why it solves the problem would improve the answer's long-term value. – Tomer Shetah Sep 23 '20 at 12:20

Add column if not exist

2 Answers2