2

Can't seem to get a simple if exists statement to work in mysql, is there a possible reason why it simply will not work???

IF EXISTS(SELECT * FROM Cookies WHERE VALUED ='2601:2c0:8403:5320:947e:a047:6e0f:e23a') BEGIN THEN END; UPDATE Cookies SET Amount = Amount + '1' WHERE VALUED ='2601:2c0:8403:5320:947e:a047:6e0f:e23a' ELSE BEGIN INSERT INTO Cookies (Valued, Amount) Values ('2601:2c0:8403:5320:947e:a047:6e0f:e23a', '1' ) END;
Improve this question
3
  • IF EXISTS(SELECT * FROM Cookies WHERE VALUED ='2601:2c0:8403:5320:947e:a047:6e0f:e23a') BEGIN THEN END; UPDATE Cookies SET Amount = Amount + '1' WHERE VALUED ='2601:2c0:8403:5320:947e:a047:6e0f:e23a' ELSE BEGIN INSERT INTO Cookies (Valued, Amount) Values ('2601:2c0:8403:5320:947e:a047:6e0f:e23a', '1' ) END; END IF; Commented May 14, 2016 at 19:32
  • edit your question don't post code in comment .. code in comment is unreadable .. Commented May 14, 2016 at 19:35
  • stackoverflow.com/questions/5528854/usage-of-mysqls-if-exists Commented May 14, 2016 at 19:36

2 Answers 2

Reset to default
2

Delete all BEGIN and END - you don't need them and you've used them incorrectly anyway:

IF EXISTS(SELECT * FROM Cookies WHERE VALUED ='2601:2c0:8403:5320:947e:a047:6e0f:e23a') THEN UPDATE Cookies SET Amount = Amount + '1' WHERE VALUED ='2601:2c0:8403:5320:947e:a047:6e0f:e23a' ELSE INSERT INTO Cookies (Valued, Amount) Values ('2601:2c0:8403:5320:947e:a047:6e0f:e23a', '1' ); END IF;

If VALUED is the primary key, you can do it much simpler:

insert into Cookies (Valued, Amount) values ('2601:2c0:8403:5320:947e:a047:6e0f:e23a', 1 ) on duplicate key update Amount = Amount + 1;
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

#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 Cookies WHERE VALUED ='2601:2c0:8403:5320:947e:a047:6e0' at line 1
@caleb you can't just execute it as a stand alone query. An IF must be in a stored procedure.
How to use this query with out using stored procedure. Simple execution.
@amee if VALUED is the primary key you can - see edited answer
Thanks sir, but i dont want to update value if there exists id in table. I just want to give the infromation. e.g. PRINT ' ID already EXISTS.'
|
0

You seem to have messed up the order of the then and begin keywords:

IF EXISTS(SELECT * FROM Cookies WHERE VALUED ='2601:2c0:8403:5320:947e:a047:6e0f:e23a') THEN -- Here! BEGIN UPDATE Cookies SET Amount = Amount + '1' WHERE VALUED ='2601:2c0:8403:5320:947e:a047:6e0f:e23a' END; ELSE BEGIN INSERT INTO Cookies (Valued, Amount) Values ('2601:2c0:8403:5320:947e:a047:6e0f:e23a', '1' ) END;

EDIT:
Assuming valued is a primary (or even just a unique) key, it would be easier to use an insert statement with an on duplicate clause:

INSERT INTO cookies (valued, amount) VALUES ('2601:2c0:8403:5320:947e:a047:6e0f:e23a', 1) ON DUPLICATE KEY UPDATE amount = amount + 1;
Improve this answer

3 Comments

That still doesn't make sense... THEN BEGIN END UPDATE ELSE? Surely the UPDATE should be before the END rather than after?
@MatBailie yikes! Got caught up in the cop-and-bug... It should, of course, be then-begin-update-end-else-insert-end (edited and fixed).
Much appreciated, that will fulfill my needs, still would like to understand why mysql will not ingest an if exists statement, I cannot find a lot of documentation on it, and was hoping someone could decipher it for me, but thanks again, saved me a lot of time!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.