Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5726
  • Last Modified:

The MySQL add if not exists for a new field.

Right now we try to add a new field to a MYSQL table but we want to check if the same field in the target table exists before we add it to avoid error message, I check from some web site (http://stackoverflow.com/questions/5069611/add-column-if-none-exists-in-mysql) it suggest:


alter table <table name> ADD IF NOT EXISTS NEW_CITY_ID bigint(19)       ;

it use if not exists but it seems MysQL see syntax error.

What should it be?
or MysQL can't do it.
0
marrowyung
Asked:
marrowyung
  • 3
  • 2
2 Solutions
 
Patrick TallaricoFSEP Systems AnalystCommented:
It looks like Mysql does not currently support the IF NOT EXISTS functionality with the ALTER TABLE syntax.

One alternate method is to run a check on the Information_Schema.COLUMNS table.  This should list all columns in the database.
Something like
SELECT COLUMN_NAME
from information_schema.COLUMNS
where TABLE_NAME = 'yourTable'
and COLUMN_NAME = 'yourNewColumnName';

If that does not return a record of your Column, then you should be alright to just issue a standard ALTER TABLE command like
ALTER TABLE `yourTable` ADD COLUMN `yourNewColumnName` bigint(19);
0
 
Julian HansenCommented:
There are a number of options here

1. Catch the error and ignore it
2. Write a stored procedure to do the work - something like this (source: http://stackoverflow.com/questions/14381895/mysql-add-column-if-not-exist)
CREATE PROCEDURE Alter_Table()
BEGIN
    DECLARE _count INT;
    SET _count = (  SELECT COUNT(*) 
                    FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE   TABLE_NAME = 'email_subscription' AND 
                            COLUMN_NAME = 'subscribe_all');
    IF _count = 0 THEN
        ALTER TABLE email_subscription
            ADD COLUMN subscribe_all TINYINT(1) DEFAULT 1,
            ADD COLUMN subscribe_category varchar(512) DEFAULT NULL;
    END IF;
END

Open in new window

3. Run a script to do what the stored procedure does and only run the query if the query against the information_schema table returns no results.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
so you both agree that MySQL don't accept the "IF NOT EXISTS " stagble for Alter table, right?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
marrowyungSenior Technical architecture (Data)Author Commented:
julianH,


What is the code to Catch the error and ignore it ? this might be ok for me as currently what I am experiencing is, I have another check that if the field has been removed and what I will do is to rename the table and clone the existing table once.

So if this process run first and the check runs later,  I will run into the sam problem as the talbe already cloned. so the same field exsiting over there.

the result can be incorrect !
0
 
Julian HansenCommented:
Personally I would go with the check to see if the column exists before hand - either in a stored procedure as shown above or executing the query to check before hand.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
I am still testing it .
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now