MySQL Error, specifically an If Exist statement

Question

I tried using from mySQL the If Exists statement but i got an error from mysql saying there's a #1064 syntax error, but i really couldn't find it. There are my codes:

If EXISTS (select * from points where username= 'john')
update points set points = "4" where username='john'
ELSE
insert into points (username, points) values ('john', 5);

What is the primary key of the `points` table? If you could explain what your logic is, you might not need to use an upsert here. — Tim Biegeleisen, Dec 01 '16 at 04:48

score 0 · Answer 1 · answered Dec 01 '16 at 05:05

You have a syntax error in your statement. You are missing "THEN" keyword after EXISTS and "END IF" at the end, and also missing a semicolon on UPDATE statement. If you still want to go with this statement, it should be like this:

IF EXISTS (select * from points where username= 'john') THEN
    UPDATE points set points = "4" where username = 'john';
ELSE
    INSERT into points (username, points) values ('john', 5);
END IF;

Please take a note that this statement can be only used in routine such as Stored Procedure or Stored Function and not in normal SQL.

On the other hand, what @TimBiegeleisen said in his answer is a more efficient way to go.

score -1 · Accepted Answer · answered Dec 01 '16 at 04:54

-1

One way to achieve your logic would be to use ON DUPLICATE KEY UPDATE while doing your INSERT:

INSERT INTO points (username, points)
VALUES ('john', 5)
ON DUPLICATE KEY UPDATE points=4

This query will insert ('john', 5') into your table, but if the primary key username john already exists, then it will points to 4.

answered Dec 01 '16 at 04:54

Tim Biegeleisen

502,043
27
286
360

1

Seriously...who is downvoting this answer without leaving any feedback whatsoever? What does this accomplish? – Tim Biegeleisen Dec 02 '16 at 15:12

MySQL Error, specifically an If Exist statement

2 Answers2