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, Jan 17 '13 at 15:04
Hey, I see ! I do have a proceedure covered on top and didn;t work as well. — phil88530, Jan 17 '13 at 15:08
Does this answer your question? [add column to mysql table if it does not exist](https://stackoverflow.com/questions/972922/add-column-to-mysql-table-if-it-does-not-exist) — Kromster, Apr 13 '20 at 14:29
possible duplicate https://stackoverflow.com/questions/972922/add-column-to-mysql-table-if-it-does-not-exist — gawkface, Oct 07 '21 at 21:04

score 32 · Answer 1 · edited Sep 21 '14 at 11:41

32

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 '14 at 11:41

Zaffy

16,801
8
50
77

answered May 06 '13 at 18:55

drsimonz

562
5
8

3

Excellent for use in _ad hoc_ scripts. – Gregory Higley Aug 24 '13 at 06:01

John Woo · Accepted Answer · 2013-01-17T15:15:31.253

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 '13 at 15:15

answered Jan 17 '13 at 15:09

John Woo

258,903
69
498
492

4

There is logical error in script, if table not exists the `Count` = 0 also. You need first check for table existence, then for column. – Hamlet Hakobyan Jan 17 '13 at 15:11
@HamletHakobyan i'm assuming that the table provided existed. – John Woo Jan 17 '13 at 15:13
1

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 Jan 17 '13 at 15:14
1

Hey bralliant, it now passes. do I have to call this function somewhere to make it running? – phil88530 Jan 17 '13 at 15:18
Actually, it's working, I just run call Alter_Table() and it works ! thanks ! – phil88530 Jan 17 '13 at 15:19

score 3 · Answer 3 · edited Apr 10 '17 at 14:16

3

You are using MS SQL Server syntax in MySQL.

edited Apr 10 '17 at 14:16

TRiG

10,148
7
57
107

answered Jan 17 '13 at 15:05

Hamlet Hakobyan

32,965
6
52
68

Hah! I knew something was off but I couldn't put my finger on it :) – Benjamin Gruenbaum Jan 17 '13 at 15:19

spencer7593 · Answer 4 · 2013-01-17T15:19:25.470

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

score 2 · Answer 5 · answered Jan 17 '13 at 15:13

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 ;

rkm432 · Answer 6 · 2017-05-19T21:53:56.977

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.

MySQL add column if not exist

6 Answers6

Linked