Reputation: 21
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
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