IF Exists Then Update MySQL

Question

I am trying to update a row if its exists, if it does not exist then I want to perform an insert into the table. The picture.id & picture.picturepath are a unique key. I have looked at some examples but I am not sure What I am doing wrong.

I have come across on duplicate key update, not sure if this is relevant to what I am trying to achieve.

Error message: 1064- You have an error in your SQL syntax near 'UPDATE picture SET picture.picturecontent = ipicturecontent WHERE picture.id at line 5"


    IF EXISTS( SELECT * FROM picture WHERE picture.id = ipictureid 
    AND picture.picturepath = ipicturepath) THEN

    UPDATE picture 
    SET picture.picturecontent = ipicturecontent
    WHERE picture.id = ipictureid 
    AND picture.picturepath = ipicturepath

    ELSE

    INSERT INTO picture (picture.id, picture.picturecontent,picture.picturepath) VALUES (ipictureid, ipicturecontent, ipicturepath)

your Question includes both the IF EXISTS and THEN keywords but your code does not. but thats just me being glib. the problem is your ELSE if refering to your WHERE picture.id = line — Bryan Devaney, Mar 04 '15 at 14:10

score 5 · Accepted Answer · edited May 24 '23 at 03:59

5

https://stackoverflow.com/a/10095812/1287480 <- Credit where credit is due.

INSERT INTO models (col1, col2, col3)
VALUES ('foo', 'bar', 'alpha')
ON DUPLICATE KEY UPDATE col3 = 'alpha';

edited May 24 '23 at 03:59

informatik01

16,038
10
74
104

answered Mar 04 '15 at 14:23

TolMera

452
10
25

Instead of using an IF THEN ELSE statement, you should use 'ON DUPLICATE KEY UPDATE' – TolMera Mar 04 '15 at 14:25

Edi G. · Answer 2 · 2015-03-04T14:48:31.807

-1

don't forget THEN, BEGIN and END

IF EXISTS( SELECT * FROM picture WHERE id = ipictureid 
    AND picturepath = ipicturepath) 
    THEN
        BEGIN
            UPDATE picture 
               SET picturecontent = ipicturecontent
             WHERE id = ipictureid 
               AND picturepath = ipicturepath
        END;
ELSE
    BEGIN
        INSERT INTO picture (id,
                             picturecontent,
                             picturepath) 
                     VALUES (ipictureid, ipicturecontent, ipicturepath)
    END;
END IF;

edited Mar 04 '15 at 14:48

answered Mar 04 '15 at 14:08

Edi G.

2,432
7
24
33

This still gives syntax error, i failed to mention this is part of a stored procedure. – Harry Mar 04 '15 at 14:13
I don't understand what you mean? – Harry Mar 04 '15 at 14:47
This seemed to work based on your answer also, BEGIN IF EXISTS( SELECT * FROM picture WHERE picture.id = ipictureid AND picture.picturepath = ipicturepath) THEN UPDATE picture SET picture.picturecontent = ipicturecontent WHERE picture.id = ipictureid AND picture.picturepath = ipicturepath; ELSE INSERT INTO picture (picture.id, picture.picturecontent, picture.picturepath) VALUES (ipictureid, ipicturecontent, ipicturepath); END IF; Thanks! – Harry Mar 05 '15 at 12:06

IF Exists Then Update MySQL

2 Answers2

Linked