Solved

The MySQL add if not exists for a new field.

Posted on 2014-01-29
6
3,945 Views
Last Modified: 2014-02-13
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
Comment
Question by:marrowyung
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 6

Assisted Solution

by:Patrick Tallarico
Patrick Tallarico earned 230 total points
ID: 39820041
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
 
LVL 57

Accepted Solution

by:
Julian Hansen earned 270 total points
ID: 39820166
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39832626
so you both agree that MySQL don't accept the "IF NOT EXISTS " stagble for Alter table, right?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 1

Author Comment

by:marrowyung
ID: 39846770
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
 
LVL 57

Expert Comment

by:Julian Hansen
ID: 39846950
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
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 39858106
I am still testing it .
0

Featured Post

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question