MySQL Syntax error for IF ... INSERT INTO

Question

I have this query in my application:

string sql = @"UPDATE AccountGroup
            SET IdGroup = @idGroup
            WHERE IdAccount = @idAccount;

            IF (ROW_COUNT() = 0) THEN
                INSERT INTO AccountGroup (IdAccount, IdGroup)
                VALUES (@idAccount, @idGroup);
            END IF;"

Which gives me this error message:

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 (ROW_COUNT() = 0) THEN INSERT INTO AccountGroup (IdAccount, IdGroup)' at line 1.

What is the problem?

Sounds like you are using SQL Server syntax, and even think you are using Microsoft's SQL Server, when the database you are running the query against is actually MySQL, which is a completely different platform from a completely different vendor. — Aaron Bertrand, Oct 25 '13 at 18:37
@AaronBertrand: Nice catch. The OP apparently didn't notice the "MySQL" in the error message. :-) — Ken White, Oct 25 '13 at 18:54
@AaronBertrand You're right, that completely flew over me. I've changed the syntax but I can't figure out what's wrong with it now. — idlackage, Oct 25 '13 at 19:22
possible duplicate of [How do I update if exists, insert if not (aka upsert or merge) in MySQL?](http://stackoverflow.com/questions/1218905/how-do-i-update-if-exists-insert-if-not-aka-upsert-or-merge-in-mysql) — dsolimano, Oct 28 '13 at 20:12

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

I don't have a MySQL server handy to test this. That said, is it possible that you're using IF() when you want to use IF?

From the documentation for IF()

IF(expr1,expr2,expr3)

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used.

And from the documentation for IF

IF search_condition THEN statement_list
   [ELSEIF search_condition THEN statement_list] ...
   [ELSE statement_list]
END IF

Which would make me think you want to write the code as something like

string sql = @"UPDATE AccountGroup
            SET IdGroup = @idGroup
            WHERE IdAccount = @idAccount;

            IF ROW_COUNT() = 0 THEN
                INSERT INTO AccountGroup (IdAccount, IdGroup)
                VALUES (@idAccount, @idGroup);
            END IF;"

Thank you, that seems like it would be the problem. I'm still getting a syntax error near that part however--any chance this could be written as a case statement? — idlackage, Oct 25 '13 at 20:12
Does this query do what you want - http://stackoverflow.com/questions/1218905/how-do-i-update-if-exists-insert-if-not-aka-upsert-or-merge-in-mysql. If so I'll vote to close as a dupe. — dsolimano, Oct 25 '13 at 21:09

MySQL Syntax error for IF ... INSERT INTO

1 Answers1