Set update row id to OUT parameter in MySQL

Question

The table tbtable contains the following columns.

The procedure to create or update an entry in tbtable is the following.

CREATE PROCEDURE `createOrUpdateTbTable` ( 
        IN `this_pid` INT UNSIGNED, 
        IN `this_sid` INT UNSIGNED, 
        IN `this_ri` LONGBLOB, 
        IN `this_defaults` TINYINT, 
        IN `this_approved` TINYINT, 
        OUT `id` INT UNSIGNED 
    ) 

    BEGIN 

        UPDATE `tbtable` SET
        `ri` = this_ri, `defaults` = this_defaults, `approved` = this_approved 
        WHERE `pid` = this_pid AND `sid` = this_sid; 

        IF ROW_COUNT() = 0  
        THEN 
        INSERT INTO `tbtable` (`pid`, `sid`, `ri`, `defaults`, `approved`) 
        VALUES (this_pid, this_sid, this_ri, this_defaults, this_approved); 
        SET id = LAST_INSERT_ID(); 
        END IF; 

    END

Right now I don't have any way to get the id of an entry when an update occurs. To what script should I change my current createOrUpdate method so that I can also retrieve the id when an update happens?

I checked other similar questions but they don't have any OUT parameter, so not applicable for my case.

Thanks.

EDIT:

BEGIN 

    IF EXISTS (SELECT*FROM `tbtable` WHERE `pid` = this_pid AND `sid` = this_sid)
    THEN
        UPDATE `tbtable` 
        SET
        `ri` = this_ri, `defaults` = this_defaults, `approved` = this_approved
        WHERE `pid` = this_pid AND `sid` = this_sid;
        SET id = `id` ;
    ELSE 
        INSERT INTO `tbtable` (`pid`, `sid`, `ri`, `defaults`, `approved`) 
        VALUES (this_pid, this_sid, this_ri, this_defaults, this_approved);
        SET id = LAST_INSERT_ID();   
    END IF;

END

I tried this approach as well, but the id is null when there is an update.

Answer 1

We could run a SELECT t.myid INTO v_id FROM t WHERE ... statement to store a value into a local procedure variable.

Or, we could set a user-defined variable.

Note that the same identifier might be used for a routine parameter, a local variable and a column. A routine parameter takes precedence over a table column.

In the general case, an UPDATE statement can affect more than one row, so we could have multiple rows. The procedure argument is a scalar, so we would need to decide which of the rows we want to return the id from.

Assuming that id column is guaranteed to be non-NULL in the (unfortunately named) tbtable table...

BEGIN
   DECLARE lv_id BIGINT DEFAULT NULL;
   -- test if row(s) exist, and fetch lowest id value of from matching rows
   SELECT t.id
     INTO lv_id   -- save retrieved id value into procedure variable
     FROM tbtable t
    WHERE t.pid = this_pid
      AND t.sid = this_sid
    ORDER BY t.id
    LIMIT 1
   ;
   -- if we got a non-NULL value returned
   IF lv_id IS NOT NULL THEN
      -- do the update
      UPDATE `tbtable` t
         SET t.ri       = this_ri
           , t.defaults = this_defaults
           , t.approved = this_approved
       WHERE t.pid = this_pid
         AND t.sid = this_sid
      ; 
   ELSE 
      INSERT INTO `tbtable` (`pid`, `sid`, `ri`, `defaults`, `approved`) 
      VALUES (this_pid, this_sid, this_ri, this_defaults, this_approved)
      ;
      SET lv_id = LAST_INSERT_ID();
   END IF;
   -- set OUT parameter
   SET id = lv_id ;
END$$

---

Note that this procedure is subject to a race condition, with a simultaneous DELETE operation from another session. Our SELECT statement could return an id for a matching row, and another session could DELETE that row, and then our update runs, and doesn't find the row. Timing here is pretty tight, it would be difficult to demonstrate this without adding a delay into the procedure, like a SELECT WAIT(15); right before the UPDATE (to give us fifteen seconds to run a delete from another session.)

Answer 2

You try to return a single value but your update statement could be executed in multiple rows. So when you return the id from that type of updated statement , you need to loop through the updated rows and return any one of those updated row values (because you expect that the combination of pid and sid is unique). Here is sample code without the rid columns as i do not want to create a temporary database with that :)

CREATE PROCEDURE createOrUpdateTbTable ( 
        IN this_pid INT UNSIGNED, 
        IN this_sid INT UNSIGNED, 
        IN this_ri LONGBLOB, 
        IN this_defaults TINYINT, 
        IN this_approved TINYINT, 
        OUT id INT UNSIGNED 
    ) 
BEGIN 
DECLARE done INT DEFAULT FALSE;
DECLARE updated_id INT;
DECLARE updatedIds CURSOR FOR SELECT tbtable.id FROM tbtableWHERE pid = this_pid AND sid = this_sid;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

IF EXISTS (SELECT*FROM `tbtable` WHERE `pid` = this_pid AND `sid` = this_sid)
THEN
    UPDATE `tbtable` 
    SET
    `defaults` = this_defaults, `approved` = this_approved
    WHERE `pid` = this_pid AND `sid` = this_sid;

    OPEN updatedIds;
    read_loop: LOOP
        FETCH updatedIds INTO updated_id;
        SET id = updated_id;
        IF done THEN
          LEAVE read_loop;
        END IF;
      END LOOP;

    CLOSE updatedIds;

ELSE 
    INSERT INTO `tbtable` (`pid`, `sid`, `defaults`, `approved`) 
    VALUES (this_pid, this_sid, this_defaults, this_approved);
    SET id = LAST_INSERT_ID();   
END IF;END

Answer 3

You need explicit return the value at the end:

 IF ROW_COUNT() = 0  
        THEN 
        INSERT INTO `tbplanhassurface` (`planid`, `surfaceid`, `roi`, `defaultsurface`, `approved`) 
        VALUES (this_planid, this_surfaceid, this_roi, this_defaultsurface, this_approved); 
        SET id = LAST_INSERT_ID(); 
 ELSE 
       SELECT @id = your_id_field
       FROM `tbplanhassurface`
       WHERE `planid` = this_planid 
         AND `surfaceid` = this_surfaceid; 
 END IF; 

  SELECT @id;

END