MySQL stored procedure If exists

Question

I'm creating a stored procedure in MySQL, and having trouble using IF EXISTS

My SQL is;

CREATE DEFINER=`##`@`%` PROCEDURE `myTestProceedure`(IN _id INT)
BEGIN
    IF EXISTS (SELECT * FROM cms.variables WHERE tmplvarid = 5 and id = _id) THEN
    BEGIN
    UPDATE cms.variables SET value = now() WHERE id = _id and tmplvarid = 5;
    END;
    ELSE 
    BEGIN
    INSERT INTO cms.variables (`tmplvarid`, `contentid`, `value`) VALUES (5, _id, now());
    END;
    END IF;
END

Basically, what I'm trying to do in this procedure is IF the row already exists in the DB, update it, otherwise insert it.

However despite whatever result SELECT * FROM cms.variables WHERE tmplvarid = 5 and id = _id gives, it just inserts into the database.

Any help would be greatly appreciated.

Answer 1

try this:

CREATE DEFINER=`##`@`%` PROCEDURE `myTestProceedure`(IN _id INT)
BEGIN
    IF (SELECT count(*) FROM cms.variables WHERE tmplvarid = 5 and id = _id)>0 THEN
    BEGIN
    UPDATE cms.variables SET value = now() WHERE id = _id and tmplvarid = 5;
    END;
    ELSE 
    BEGIN
    INSERT INTO cms.variables (`tmplvarid`, `contentid`, `value`) VALUES (5, _id, now());
    END;
    END IF;
END