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
– Mouser, Commented Feb 15, 2015 at 12:11
possible duplicate of Insert to table or update if exists (MySQL) — mario
– mario, Commented Feb 15, 2015 at 12:21
@ Mouser Sir, the error details i posted is the result i run in my phpmyadmin. — user4568690
– user4568690, Commented Feb 15, 2015 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
– user4568690, Commented Feb 15, 2015 at 12:36

Gordon Linoff · Accepted Answer · 2015-02-15 12:45:56Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related