How can I perform this MySql Statement in a loop-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

You can try something like this (please replace datatype(length) with the type of custom7)

DECLARE @count INT;    
SET @count = 1;        
WHILE @count<= 100    
     BEGIN
   
          UPDATE refs SET custom_7 = CAST(@count AS **datatype(length)**) WHERE custom_7 = '' LIMIT 220;    
          SET @count = @count   1;    
     END;