MYSQL How to alter table if column doesnt exist

Question

I am using SQL which doesnt support INFORMATION_SCHEMA.COLUMNS. My code, which doesnt work

ALTER TABLE Report ADD IF NOT EXISTS LastName CHAR(25);

this works

ALTER TABLE Report ADD LastName CHAR(25);

which part is wrong?

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
               WHERE TABLE_NAME  = 'Report' AND COLUMN_NAME = 'LastName' ) 
    ALTER TABLE 'Report' ADD 'LastName' CHAR(25);

This SO discussion could be answer to your question http://stackoverflow.com/questions/133031/how-to-check-if-column-exists-in-sql-server-table — Pratik, May 14 '14 at 08:45
my sql doesnt support FROM INFORMATION_SCHEMA.COLUMNS !! READ — BeyondProgrammer, May 14 '14 at 08:46
my bad i didn't ask you about database..my answer was considering SQL Server. — Pratik, May 14 '14 at 08:48
I am cracking my head and cant find any solution online, i know there is CREATE TABLE IF NOT EXIST but i need to create column if not exist — BeyondProgrammer, May 14 '14 at 08:53
Is it [tag:h2] still, or some other database system? Almost all SQL database systems have an appropriate tag, and adding that tag to your question would probably be quite helpful. — Damien_The_Unbeliever, May 14 '14 at 08:53
Although i never used mysql bt the manual suggests it has informationschema table...give a try to this link http://dev.mysql.com/doc/refman/5.0/en/information-schema.html — Pratik, May 14 '14 at 08:55
@BeyondProgrammer No need to get rude to Pratik - esp. considering you didn't mention the RDBMS you're using in your question. And you didn't tag your question accordingly, although (as has already been pointed out) this information is crucial for answering the question. — Frank Schmitt, May 14 '14 at 09:09
I thought MySQL did support INFORMATION_SCHEMA.COLUMNS? Certainly does according to the [documentation here](http://dev.mysql.com/doc/refman/5.0/en/information-schema.html). Are you using an old version? If so, might be helpful to state which version. — Steve Pettifer, May 14 '14 at 09:23
Well INFORMATION_SCHEMA has been supported certainly since 5.0. — Steve Pettifer, May 14 '14 at 09:27
MySQL *does* support `information_schema.columns`: http://sqlfiddle.com/#!8/6eb12/1 but your SQL is invalid. String literals need to go between single quotes. — , May 14 '14 at 09:37

score 1 · Answer 1 · answered May 14 '14 at 09:34

1

You need to quote the object names in the statement.

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
           WHERE TABLE_NAME  = 'Report' AND COLUMN_NAME = 'LastName' ) 
ALTER TABLE MLReport ADD LastName CHAR(25);

answered May 14 '14 at 09:34

Steve Pettifer

1,975
1
19
34

1

I think single quotes is right for MySQL - it's been a while, please someone correct me if not. – Steve Pettifer May 14 '14 at 09:35
Yes, single quotes are used in SQL to denote a string literal – May 14 '14 at 09:38
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' at line 1 */ – BeyondProgrammer May 14 '14 at 09:38
OK, my bad - I've not used MySQL in a while - [this SO post](http://stackoverflow.com/questions/5528854/usage-of-mysqls-if-exists) should see you right with regards to the `EXISTS` keyword – Steve Pettifer May 14 '14 at 09:40

MYSQL How to alter table if column doesnt exist

1 Answers1