[FIXED] How can I perform this MySql Statement in a loop

Issue

I would write my cmd line statements more sophistically. How can this be done by using a loop?

     update refs set custom_7 = '';
     update refs set custom_7='1' where custom_7 = '' limit 220 ;
     update refs set custom_7='2' where custom_7 = '' limit 220 ;
     update refs set custom_7='3' where custom_7 = '' limit 220 ;
     ...
     update refs set custom_7='100' where custom_7 = '' limit 220 ;

thanks a lot.

Solution

If there is a column, like an id, that defines the order of the rows by which you want to update the rows, use ROW_NUMBER() window function to rank the rows and join to the table:

WITH cte AS (SELECT *, ROW_NUMBER() OVER (ORDER BY id) rn FROM refs)
UPDATE refs r
INNER JOIN cte c ON c.id = r.id
SET r.custom_7 = (c.rn - 1) DIV 220 + 1
WHERE c.rn <= 100 * 220; -- remove the WHERE clause if there is actually no limit to the values of custom_7

If there is no column like the id, you may remove ORDER BY id from the OVER() clause of ROW_NUMBER(), but then the rows will be updated arbitrarily.

See a simplified demo.

Answered By - forpas

Answer Checked By - David Goodson (PHPFixing Volunteer)