Add a column if not exists in table

Question

I want to add a column which will be added in table only if column with same name is not exists.
Alter Table tablename add col varchar(250)

There is no IF NOT EXIST for adding columns, you will have to check this in a separate Query — freytag, Feb 28 '14 at 08:30

Pablo Francisco Pérez Hidalgo · Accepted Answer · 2014-02-28T09:40:57.720

I think you cant just execute your SQL and retrieve the error if the column already exists.

On the other hand, if you don't want to reach a possible error situation at the MySQL side you could try something like (dynamic SQL):

SET @query = (SELECT
    IF((SELECT COUNT(1) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=database() AND      
     TABLE_NAME = 'YOUR_TABLE' AND COLUMN_NAME = 'COL')=0,
     CONCAT('ALTER TABLE YOUR_TABLE ADD COLUMN COL varchar(255)'),
 'SELECT ''ALREADY EXISTS'''
 ));
PREPARE st FROM @query;
EXECUTE st;

First, you will generate a SQL containing the column addition SQL or a dummy SQL depending on whether you already have the target column at the INFORMATION_SCHEMA.

PREPARE

and

EXECUTE

statements are used to execute that query.

score 0 · Answer 2 · answered Feb 28 '14 at 08:33

0

use INFORMATION_SCHEMA database

SELECT 
count(*) 
FROM
information_schema.COLUMNS
WHERE
information_schema.TABLE_SCHEMA=DB name
AND information_schema.TABLE_NAME=table name
AND information_schema.COLUMN_NAME=col name

if count is more than 0 create the column else don't

answered Feb 28 '14 at 08:33

avisheks

1,178
10
27

Can you please add code for checking count in IF – Sonam Mohite Feb 28 '14 at 09:01
@user1280492 You can't check it in a single SQL outside a stored procedure or a dynamic sql. – Pablo Francisco Pérez Hidalgo Feb 28 '14 at 09:39

Add a column if not exists in table

2 Answers2