Created
August 30, 2022 17:17
-
-
Save tzkmx/ee5127124a30129733bc43aee14d1862 to your computer and use it in GitHub Desktop.
Cursor Mysql update row by row
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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