CODEWITHSUNDEEP
phpmysql
bhushans12
bhushans12

Reputation: 21

Mysql script to create a column

I need a script which checks if the columns exists in the database, and if not create the missing column(s). will the code under fits exactly to my requirement?

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘management’ AND COLUMN_NAME = ‘lastname’)
BEGIN
  ALTER TABLE TEST ADD lastname VARCHAR
END

Upvotes: 2

Views: 194

Answers (1)

Naruto
Naruto

Reputation: 4329

Note that INFORMATION_SCHEMA isn't supported in MySQL prior to 5.0. Nor are stored procedures supported prior to 5.0, so if you need to support MySQL 4.1, this solution isn't good.

    IF NOT EXISTS( SELECT NULL
                FROM INFORMATION_SCHEMA.COLUMNS
               WHERE table_name = 'management'
                 AND table_schema = 'db_name'
                 AND column_name = 'lastname')  THEN

      ALTER TABLE `management ` ADD `lastname ` VARCHAR(255) NOT NULL;

END IF;

Another solution would be to just try the 

ALTER TABLE ADD lastname VARCHAR(255) NOT NULL;

. It should throw an error if the column already exists.

ERROR 1060 (42S21): Duplicate column name 'lastname' Catch the error and disregard it in your upgrade script.

Upvotes: 0

Related Questions