MySQL update tables in batches 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.
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 &gt; @event_id AND event_id &lt;= @event_id + 1000) DO

    UPDATE geo_opt_2017.event SET event_lat_int = event_lat_int * 1 WHERE event_id &gt; @event_id AND event_id &lt;= @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.