MySQL update tables in batches

Question

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.

Answer 1

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.

Answer 2

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.)

Answer 3

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