I want to update my MySQL table with batches.
The table has 10000000+ records, so I want to update first 1000 rows, once it is done then update 1001 to 2000 rows. I need to make it as a loop.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityI want to update my MySQL table with batches.
The table has 10000000+ records, so I want to update first 1000 rows, once it is done then update 1001 to 2000 rows. I need to make it as a loop.
I use this form for delete obsolete data monthly day by day, but for update it will work also:
adopted for UPDATE procedure example:
DELIMITER $$
CREATE DEFINER = `root`@`localhost` PROCEDURE `geo_opt_2017`.`udp_batch_update`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
SET @event_id = 1;
WHILE EXISTS (SELECT event_id FROM geo_opt_2017.event WHERE event_id > @event_id AND event_id <= @event_id + 1000) DO
UPDATE geo_opt_2017.event SET event_lat_int = event_lat_int * 1 WHERE event_id > @event_id AND event_id <= @event_id + 1000 ;
SET @event_id = @event_id + 1000;
END WHILE;
END $$
DELIMITER ;
SET portion - is dummy, just for illustration, You need replace it for real procedure logic
The logic will proper work with many numeric or dates - simple increment by numeric or date time interval.
with tables where PK - combined from different columns without any visible logic, like telecom tables - (prefix, context, priority) it also possible, but little more complicated.
Do not use OFFSET
. The best way is to use the PRIMARY KEY
and "remember where you left off". I spell out the details here. (That's aimed at deletes, but is easily adaptable to updates.)
Execute below sript on db and read the inline comments for explanation of scripts
/*Below table is used for batch processing progress tracking*/
#SELECT * FROM BatchProcessing_Details
DROP TABLE IF EXISTS BatchProcessing_Details;
CREATE TABLE BatchProcessing_Details
(
Id BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,
FromNo BIGINT DEFAULT 0,
ToNo BIGINT DEFAULT 0,
ProcessedRows BIGINT,
CreatedOn DATETIME
);
DELIMITER $$
DROP PROCEDURE IF EXISTS sp_batch_insert$$
CREATE PROCEDURE sp_batch_insert()
BEGIN
DECLARE FromNo BIGINT DEFAULT 0; /*Used for StartPosition*/
DECLARE ToNo BIGINT DEFAULT 10000; /*Used for EndPosition*/
DECLARE BatchCount BIGINT DEFAULT 10000; /*used for processing no of rows per batch*/
DECLARE MaxId BIGINT;
SET MaxId=(SELECT MAX(id) FROM tblname); /*upto this the loop will run*/
myloop: WHILE ToNo <= MaxId DO
UPDATE tblName SET col1='values'
WHERE Id BETWEEN FromNo AND ToNo;
/*
For Insert Use below (Id Must be Identity Column)
INSERT INTO tblName(col1,col2,col3)
SELECT col1,col2,col3 FROM tblname WHERE Id BETWEEN FromNo AND ToNo;
For delete use below (Id Must be Identity Column)
DELETE FROM tblName
WHERE Id WHERE Id BETWEEN FromNo AND ToNo;
*/
INSERT INTO BatchProcessing_Details(FromNo,ToNo,CreatedOn) /*Using this table we can track processing*/
SELECT FromNo,ToNo,NOW();
IF ToNo=MaxId THEN /*If ToNo=MaxId means we have done need to get out of the loop*/
LEAVE myloop;
END IF;
SET FromNo=ToNo+1;
SET ToNo=CASE WHEN ToNo+BatchCount >= MaxId THEN MaxId /*if ToNo greater than or equl to MaxId it means no records remain for processing in next batch*/
ELSE ToNo+BatchCount
END;
END WHILE myloop;
END $$
DELIMITER;
CALL sp_batch_insert();
#SELECT * FROM BatchProcessing_Details