MYSQL Procedures - INSERT INTO Return value

Question

I have the MYSQL stored procedure defined as shown below:

DROP PROCEDURE IF EXISTS TEST_LOCATION;

CREATE PROCEDURE `TEST_LOCATION`()
   BEGIN
        DECLARE VCount tinyint(1) DEFAULT 1;

        INSERT INTO my_counts_model (type,code,model_code,count) 
        VALUES      ( 1,1,456,1) 
        ON DUPLICATE KEY UPDATE count = count + 1;     
 END;

The procedure updates the count if the type,code,model_code has the value in table, otherwise inserts a new record. I mean the key is on type,code,model_code.

I want to get whether it is a new insert or the count update for further operations. I am trying to check for the return value of the INSERT INTO, but couldn't find syntax or any solution.

Eugen Rieck · Accepted Answer · 2011-12-29 17:27:37Z

1

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_row-count

"ROW_COUNT(): For INSERT ... ON DUPLICATE KEY UPDATE statements, the affected-rows value is 1 if the row is inserted as a new row and 2 if an existing row is updated."

answered Dec 29, 2011 at 17:27

Eugen Rieck

65.6k10 gold badges75 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bujji Over a year ago

Thank You for pointing me to the correct link . I just checked and it is working fine when I run the queries separately , But in stored procedures some how I am getting 3 if the key exists otherwise 1 . I am not sure why I am getting 3 in stored procedure. I am still debugging it

Bujji Over a year ago

It is a toad issue , If I run the procedure using toad I am getting the result as 3 and otherwise I am getting two as expected . Thank You again

Devart · Accepted Answer · 2011-12-29 17:45:01Z

0

You also can do something like this -

add new 'flag' field to the table and set 1 on INSERT or 2 on UPDATE, e.g.

INSERT INTO my_counts_model (type, code, model_code, count, flag) 
VALUES ( 1, 1, 456, 1, 1) 
ON DUPLICATE KEY UPDATE count = count + 1, flag = 2;

In this case the information will be stored in the table.

answered Dec 29, 2011 at 17:45

Devart

120k23 gold badges172 silver badges189 bronze badges

Collectives™ on Stack Overflow

MYSQL Procedures - INSERT INTO Return value

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related