1

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.

1
  • Ok, I made the script myself but it's based on Identity rows. Update table set c='value' where id between n and n1.
    – TheDataGuy
    May 20, 2017 at 8:51

3 Answers 3

3

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.

0

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

0

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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.