IF EXISTS statement giving error #1064

Question

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;

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; — Caleb DuBach, May 14 '16 at 19:32
edit your question don't post code in comment .. code in comment is unreadable .. — ScaisEdge, May 14 '16 at 19:35
http://stackoverflow.com/questions/5528854/usage-of-mysqls-if-exists — Pevara, May 14 '16 at 19:36

Bohemian · Answer 1 · 2016-07-11T17:14:51.040

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;

edited Jul 11 '16 at 17:14

answered May 14 '16 at 19:38

Bohemian

412,405
93
575
722

#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 DuBach May 14 '16 at 19:44
1

@caleb you can't just execute it as a stand alone query. An `IF` must be in a stored procedure. – Bohemian May 14 '16 at 19:46
How to use this query with out using stored procedure. Simple execution. – Amee Jul 11 '16 at 14:54
@amee if `VALUED` is the primary key you can - see edited answer – Bohemian Jul 11 '16 at 17:15
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.' – Amee Jul 11 '16 at 18:22
@Amee either create a unique index on the column (easiest and best, because it covers all types of data changes - ie insert or update), or `... on duplicate key signal sqlstate '1234' message_text = 'ID already exists'` – Bohemian Jul 11 '16 at 19:07
- on duplicate key signal sqlstate '1234' message_text = 'ID already exists' .. This isn't working. – Amee Jul 11 '16 at 22:10
That means we have no any option ? – Amee Jul 11 '16 at 22:51
@Amee I would `create unique index idx1 on mytable(mycolumn)`. It will cause an sqlexception if someone tries to insert a duplicate – Bohemian Jul 11 '16 at 23:04

Mureinik · Answer 2 · 2016-05-14T19:44:37.770

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;

edited May 14 '16 at 19:44

answered May 14 '16 at 19:35

Mureinik

297,002
52
306
350

That still doesn't make sense... `THEN BEGIN END UPDATE ELSE`? Surely the `UPDATE` should be before the `END` rather than after? – MatBailie May 14 '16 at 19:44
@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). – Mureinik May 14 '16 at 19:45
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! – Caleb DuBach May 14 '16 at 19:52

IF EXISTS statement giving error #1064

2 Answers2