CODEWITHSUNDEEP
mysql
Harry
Harry

Reputation: 3195

IF Exists Then Update MySQL

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)

Upvotes: 2

Views: 11937

Answers (2)

TolMera
TolMera

Reputation: 416

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';

Upvotes: 5

Edi G.
Edi G.

Reputation: 2420

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;

Upvotes: -1

Related Questions