MySQL add column if not exist

Question

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'email_subscription' AND COLUMN_NAME = 'subscribe_all')
  THEN 
  ALTER TABLE email_subscription
  ADD COLUMN subscribe_all TINYINT(1) DEFAULT 1,
  ADD COLUMN subscribe_category varchar(512) DEFAULT NULL;

I had a look at huge amount of examples. but this query doesn't work, I got error of:

ERROR 1064 (42000): 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 NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =' at line 1

Are you doing this inside a stored procedure? The IF ELSE isn't valid outside a procedure or function. — Michael Berkowski
– Michael Berkowski, Commented Jan 17, 2013 at 15:04
Hey, I see ! I do have a proceedure covered on top and didn;t work as well. — phil88530
– phil88530, Commented Jan 17, 2013 at 15:08
Does this answer your question? add column to mysql table if it does not exist — Kromster
– Kromster, Commented Apr 13, 2020 at 14:29

user823738 · Accepted Answer · 2014-09-21 11:41:48Z

33

If your host doesn't give you permission to create or run procedures, I think I found another way to do this using PREPARE/EXECUTE and querying the schema:

SET @s = (SELECT IF(
    (SELECT COUNT(*)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_name = 'table_name'
        AND table_schema = DATABASE()
        AND column_name = 'col_name'
    ) > 0,
    "SELECT 1",
    "ALTER TABLE table_name ADD col_name VARCHAR(100)"
));

PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

edited Sep 21, 2014 at 11:41

user823738

17.6k8 gold badges57 silver badges78 bronze badges

answered May 6, 2013 at 18:55

drsimonz

5795 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gregory Higley Over a year ago

Excellent for use in ad hoc scripts.

John Woo · Accepted Answer · 2013-01-17 15:15:31Z

24

you can create a procedure for the query,

DELIMITER $$
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 $$
DELIMITER ;

edited Jan 17, 2013 at 15:15

answered Jan 17, 2013 at 15:09

John Woo

265k70 gold badges509 silver badges500 bronze badges

5 Comments

Hamlet Hakobyan Over a year ago

There is logical error in script, if table not exists the Count = 0 also. You need first check for table existence, then for column.

John Woo Over a year ago

@HamletHakobyan i'm assuming that the table provided existed.

phil88530 Over a year ago

I got 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 '@count INT;

phil88530 Over a year ago

Hey bralliant, it now passes. do I have to call this function somewhere to make it running?

phil88530 Over a year ago

Actually, it's working, I just run call Alter_Table() and it works ! thanks !

TRiG · Accepted Answer · 2017-04-10 14:16:48Z

4

You are using MS SQL Server syntax in MySQL.

edited Apr 10, 2017 at 14:16

TRiG

10.7k9 gold badges62 silver badges113 bronze badges

answered Jan 17, 2013 at 15:05

Hamlet Hakobyan

33.5k6 gold badges55 silver badges70 bronze badges

1 Comment

Benjamin Gruenbaum Over a year ago

Hah! I knew something was off but I couldn't put my finger on it :)

spencer7593 · Accepted Answer · 2013-01-17 15:19:25Z

3

There is no equivalent syntax to achieve this in a single MySQL statement.

To get something simlilar, you can either

1) attempt to add the column with an ALTER TABLE, and let MySQL raise an error if a column of that name already exists in the table, or

2) query the information_schema.columns view to check if a column of that name exists in the table.

Note that you really do need to check for the table_schema, as well as the table_name:

SELECT column_name
  FROM information_schema.columns 
 WHERE table_schema = 'foo'
   AND table_name   = 'email_subscription'
   AND column_name  = 'subscribe_all'

and based on that, decide whether to run the ALTER TABLE

edited Jan 17, 2013 at 15:19

answered Jan 17, 2013 at 15:13

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Comments

Saharsh Shah · Accepted Answer · 2013-01-17 15:13:45Z

2

Also add condition for database name to check column existance.

Try this:

DELIMITER $$
CREATE PROCEDURE sp_AlterTable()
BEGIN
    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
                   WHERE TABLE_SCHEMA = 'dbName' AND 
                         TABLE_NAME = 'email_subscription' AND 
                         COLUMN_NAME = 'subscribe_all') THEN 
       ALTER TABLE email_subscription
          ADD COLUMN subscribe_all TINYINT(1) DEFAULT 1,
          ADD COLUMN subscribe_category VARCHAR(512) DEFAULT NULL;
    END IF; 
END $$
DELIMITER ;

answered Jan 17, 2013 at 15:13

Saharsh Shah

29.1k9 gold badges53 silver badges85 bronze badges

Comments

rkm432 · Accepted Answer · 2017-05-19 21:53:56Z

0

SET @s = (SELECT IF(
    (SELECT COUNT(column_name)
          FROM INFORMATION_SCHEMA.COLUMNS
          WHERE table_name = 'oc_bitcoin_wallets_receive'
          AND table_schema = 'k_opencart2'
          AND column_name = 'test3'
    ) > 0,
    "SELECT 1",
    "ALTER TABLE `oc_bitcoin_wallets_receive` ADD COLUMN `test3` INT NOT NULL AFTER `test2`;"
));
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Values for edit: oc_bitcoin_wallets_receive - table name, k_opencart2 - database name, test3 - name of new column, oc_bitcoin_wallets_receive - second location table test3 - second location column, test2 - name of column before new column.

edited May 19, 2017 at 21:53

answered May 19, 2017 at 21:44

rkm432

12 bronze badges

Comments

Powerstone · Accepted Answer · 2024-05-31 19:59:24Z

0

I am late to the party but a universal solution that works for all mysql servers is;

$query = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'yourtablename' AND table_schema = DATABASE() AND column_name = 'yourcolumnname'"; $result=mysqli_query($link, $query); if(mysqli_num_rows($result) == 0){ $query2 = ("ALTER TABLE yourtablename ADD yourcolumname VARCHAR( 100 ) NOT NULL"); $create = mysqli_query($link, $query2); }

answered May 31, 2024 at 19:59

Powerstone

1064 bronze badges

Collectives™ on Stack Overflow

MySQL add column if not exist

7 Answers 7

1 Comment

5 Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

1 Comment

5 Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related