Search code examples
mysqlmysql-error-1064

IF statement in MySQL results in Error Code 1064

I am trying to nest an if statement in what will eventually become a routine.

SET @foo = (SELECT foo FROM table WHERE id = 1);
SET @bar = (SELECT bar FROM table WHERE id = 1);

IF @foo = @bar THEN SET @thisVar = 1;
ELSE SET @thisVar = 0;
END IF

SELECT @thisVar;

But I get:

Error Code: 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 @foo = @bar THEN SET @thisVar = 1;'at line 1)

What am I doing wrong?

Solution

  • Not exactly sure what's up there, but why does the error say line 1? Perhaps the issue is that you're not actually running the whole procedure.

    You can try using the IF() function like this:

    SET @thisVar = IF(@foo = @bar, 1, 0);

    I think this is shorter and cleaner for simple if/else assignments.