0

Possible Duplicate:
MySQL: How to add a column if it doesn't already exist?

A tool I use is running this query but it's failing. I'm trying to help debug but cannot figure out what's wrong:

IF EXISTS( SELECT NULL FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_name = 'TOWNY_RESIDENTS' 
AND table_schema = 'minecraft'
AND column_name != 'town-ranks') 
THEN
ALTER TABLE TOWNY_RESIDENTS (ADD 
`town-ranks`  mediumtext,
`nation-ranks`  mediumtext
);

The inner select query works fine. It seems the if exists syntax is wrong but I can't figure out how. Examples on website like SO show similar ideas...

The error 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 NULL FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'TOW' at line 1
Community
  • 1
  • 1
helion3
  • 34,737
  • 15
  • 57
  • 100

1 Answers1

0

ALTER TABLE does not support IF EXISTS in MySQL. You can run your ALTER TABLE statement and ignore the resulting errors.

Another option is to do a SELECT INTO OUTFILE on information_schema to generate the ALTER TABLE statement if necessary, and then source that file to execute the ALTER TABLE statement.

Ike Walker
  • 64,401
  • 14
  • 110
  • 109
  • Is there any query-only solution to check? We could just run alter without checking, or SHOW COLUMNS and parsing the output for the field, but I'm just covering my bases. – helion3 Aug 07 '12 at 16:53
  • Because even this doesn't work: IF NOT EXISTS( (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'TOWNY_RESIDENTS') ) THEN SELECT 1 FROM TOWNY_RESIDENTS; – helion3 Aug 07 '12 at 16:59
  • There are dozens of mysql threads indicating that alter table works fine with the if exists... – helion3 Aug 07 '12 at 17:00