How to check if a column exist inside a table, and preform action based on the result

Question

I use MySQL 5.5.16.

I'm trying to check if a certain column exists in one of my tables. If it is I want to update its type & collation and if it isn't I want to add it to the table.

I've read similar posts Here and Here about IF EXISTS statement. But I'm getting an error with my query:

IF EXISTS (SELECT * FROM information_schema.columns WHERE table_name = 'e4s_account_details' AND column_name = 'accountID') THEN
   ALTER TABLE 'e4s_account_details' CHANGE 'accountID' 'accountID' INT(10) NOT NULL AUTO_INCREMENT;
ELSE 
   ALTER TABLE 'e4s_account_details' ADD COLUMN 'accountID' INT(10) NOT NULL AUTO_INCREMENT;
END IF;

The error I get is:

1064 - 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 EXISTS (SELECT * FROM information_schema.columns WHERE table_name = 'e4s_acco' at line 1

BTW, If I only query this:

(SELECT * FROM information_schema.columns WHERE table_name = 'e4s_account_details' AND column_name = 'accountID');

I get no error and the table+column information is returned.

http://stackoverflow.com/questions/8219714/check-if-column-exists-before-alter-table-mysql — Haim Evgi, Apr 01 '12 at 06:01

score 0 · Accepted Answer · answered Apr 01 '12 at 06:03

0

You can't use this syntax directly with select query instead you can make stored procedure or query with information schema table for doing same.

answered Apr 01 '12 at 06:03

Thanks for the help, 2 more little questions: can a stored procedure be executed from a `.sql` file ? and, What do you mean by "query with `information schema` table for doing same" ? – Alon Adler Apr 01 '12 at 06:24
for 1st yes we can execute and for 2nd check this link http://stackoverflow.com/questions/173814/mysql-alter-syntax-to-drop-a-column-if-it-exists – Apr 01 '12 at 06:53

How to check if a column exist inside a table, and preform action based on the result

1 Answers1