What’s causing the syntax error in the IF EXISTS line in my SQL query?

Question

I’m running this SQL query:

IF EXISTS (SELECT * FROM score WHERE username='ms')
UPDATE score SET score='30' WHERE username='ms'
ELSE 
INSERT INTO score (username,score) VALUES ('ms','30')

but I keep getting this syntax error:

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 * FROM score WHERE username='ms') UPDATE score SET score='30' at line 1

What am I doing wrong?

Try to run this in your *phpmyadmin* SQL environment. That should give you more information. However `IF` statements can only be used in `stored procedures` and `functions` on MySQL — Mouser, Feb 15 '15 at 12:11
possible duplicate of [Insert to table or update if exists (MySQL)](http://stackoverflow.com/q/4205181) — mario, Feb 15 '15 at 12:21
@ Mouser Sir, the error details i posted is the result i run in my phpmyadmin. — user4568690, Feb 15 '15 at 12:31
@mario sir,i run INSERT INTO score (username,score) VALUES ('ms','30') ON DUPLICATE KEY UPDATE score SET score='30' WHERE username='ms',returns #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 'SET score='30' WHERE username='ms'' at line 1, still syntax error — user4568690, Feb 15 '15 at 12:36
@mario thank you very much! it did solve the problems but i still don't know why my code had syntax error. — user4568690, Feb 15 '15 at 12:45

score 1 · Accepted Answer · answered Feb 15 '15 at 12:45

if as control flow is only allowed in programming blocks in stored procedures, functions, and triggers. This should not be confused with the function if(), a non-standard MySQL extension that is allowed in any expression.

However, you can do what you want using replace or on duplicate key update. First, create a unique index on username:

create unique index idx_score_username on score(username);

Then do the insert as:

INSERT INTO score (username, score)
    VALUES ('ms', 30)
    ON DUPLICATE KEY UPDATE score = VALUES(score);

Note that I removed the single quotes from '30'. If score1 is numeric (a reasonable assumption I think), then use a numeric constant rather than a string constant.

What’s causing the syntax error in the IF EXISTS line in my SQL query?

1 Answers1