mySQL - If statement giving syntax error

Question

So I have the following query:

IF ( ( SELECT RevisionNumber FROM SchemaVersion ) > 1 ) THEN BEGIN
    CREATE TABLE Test ( ID INT );
    CREATE TABLE Test2 ( ID INT );
END;
END IF

Basically I want to decide based on a value in my table (SchemaVersion - always has 1 row) whether or not I need to make certain changes.

However, when I use this query I get the following error message:

#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 ( ( SELECT RevisionNumber FROM SchemaVersion ) > 1 ) THEN BEGIN
 CREATE T' at line 1

Any idea why this is erroring / how to fix?

I was just reading another post and apparently BEGIN / END are only allowed within stored procs. Is there any way to get around this (without putting it in a stored proc).

Thanks

grossvogel · Accepted Answer · 2011-10-02 21:30:03Z

17

The IF statement is part of what's called Compound-statement Syntax, and it is only available inside stored code, like a trigger, function, or stored procedure.

http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-compound-statements.html

answered Oct 2, 2011 at 21:30

grossvogel

6,7921 gold badge28 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

judda Over a year ago

Thanks! Not the answer I wanted (for obvious reasons) but points out the issue.

grossvogel Over a year ago

@judda: Since it looks like you are trying to do conditional schema updates, I'll offer a suggestion: You could write an update script that creates a stored procedure, calls it, and then drops it. Inside the procedure, you can use IF ... THEN only apply schema changes that aren't already applied.

judda Over a year ago

This script is being generated through a bunch of flat files (i.e. ALTER statements) and then just being echoed together. Right now they are all being combined into one big SQL file which is then used. I guess I could have something that prefixes it with the create procedure and everything calls and then just give a link for that instead of the flat file it is based off of. Great idea :)

Maxim Krizhanovsky · Accepted Answer · 2011-10-02 21:26:00Z

4

Using IF with brackets calls the IF function, which is different from IF statement

Note There is also an IF() function, which differs from the IF statement described here. See Section 11.4, “Control Flow Functions”.

answered Oct 2, 2011 at 21:26

Maxim Krizhanovsky

26.8k5 gold badges62 silver badges91 bronze badges

Comments

imm · Accepted Answer · 2011-10-02 21:26:20Z

1

I think you would need to run that within a stored procedure. See http://dev.mysql.com/doc/refman/5.0/en/stored-routines.html

answered Oct 2, 2011 at 21:26

imm

5,9391 gold badge29 silver badges32 bronze badges

Collectives™ on Stack Overflow

mySQL - If statement giving syntax error

3 Answers 3

3 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Related