Skip to content

Instantly share code, notes, and snippets.

@tzkmx
Created August 30, 2022 17:17
Show Gist options
  • Save tzkmx/ee5127124a30129733bc43aee14d1862 to your computer and use it in GitHub Desktop.
Save tzkmx/ee5127124a30129733bc43aee14d1862 to your computer and use it in GitHub Desktop.
Cursor Mysql update row by row
DELIMITER $$
CREATE PROCEDURE fixVersionAggregate ()
BEGIN
DECLARE finished INTEGER DEFAULT 0;
DECLARE evId BIGINT DEFAULT 0;
DECLARE evVersion INT DEFAULT 0;
-- declare cursor for
DEClARE curEvent
CURSOR FOR
SELECT id, aggregate_version FROM stored_events
WHERE aggregate_uuid = '894fbbb8-d9b7-48ce-b6ce-1b0ab9cbd843'
AND aggregate_version >= 40;
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET finished = 1;
OPEN curEvent;
getEvent: LOOP
FETCH curEvent INTO evId, evVersion;
IF finished = 1 THEN
LEAVE getEvent;
END IF;
-- build email list
UPDATE stored_events
SET aggregate_version = (evVersion - 1)
WHERE id = evId;
END LOOP getEvent;
CLOSE curEvent;
END$$
DELIMITER ;
CALL fixVersionAggregate();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment