mysql, syntax issue in procedure

Question

I have problems with syntax in mysql procedure

CREATE PROCEDURE savep(
        IN p_uuid  VARCHAR(36),
        IN p_sp    INTEGER,
        IN p_cd    VARCHAR(250)
        ) 
BEGIN 
           INSERT INTO maintable 
                (
                    uuid, 
                    sp,
                    cd,
                    last_time_saved
                )
           VALUES
                (
                    p_uuid,  
                    p_sp,
                    p_cd,
                    now()
                )
           ON DUPLICATE KEY UPDATE
                sp =   VALUES(p_sp),
                cd =   VALUES(p_cd),
                cd =   VALUES(now()); -- syntaxerror, unexcepted NOW_SYM
        END -- syntax error, unexcepted END

what am i doing wrong? uuid is a primary key in the main table.

Kickstart · Accepted Answer · 2014-09-11 16:00:29Z

Two minor things I can see.

You have no delimiter set at the top (but you might have that in the version you are trying to use).

Secondly you are setting cd twice in the on duplicate key clause, and one of those is setting it to VALUES(now()) , where what should be in the brackets is the name of a column whose value from the VALUES clause of the insert that you are setting it to.

Try this:-

DELIMITER //
CREATE PROCEDURE savep(
        IN p_uuid  VARCHAR(36),
        IN p_sp    INTEGER,
        IN p_cd    VARCHAR(250)
        ) 
BEGIN 
           INSERT INTO maintable 
                (
                    uuid, 
                    sp,
                    cd,
                    last_time_saved
                )
           VALUES
                (
                    p_uuid,  
                    p_sp,
                    p_cd,
                    now()
                )
           ON DUPLICATE KEY UPDATE
                sp =   VALUES(p_sp),
                cd =   VALUES(p_cd),
                last_time_saved =   now();
        END

Collectives™ on Stack Overflow

mysql, syntax issue in procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related