CODEWITHSUNDEEP
pythonmysqlsqldatabaseiteration
user144700
user144700

Reputation: 60

Update table with loop

Hi I would like to update a column by selecting minimum value in that column in each iteration and increasing it by 10% till the total sum of column becomes a particular value (say 3000)

UPDATE db1.tb1 a 
SET    a.col1 = IF(SUM(a.col1) < 3000, a.col1 + ( a.col1 / 10 ), a.col1) 
WHERE  a.col1 IN (SELECT Min(b.col1) 
                  FROM   (SELECT col1, 
                                 col2 
                          FROM   db1.tb1), 
                         b)

Upvotes: 0

Views: 349

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I don't think you can do this in a single update statement. You can repeated loop. Each iteration would look more like this:

update tb1.tbl t cross join
       (select min(col1) as min1, sum(col1) as sum1
        from tb1.tbl
       ) tt
    set col1 = 1.1 * col1
    where tt.sum1 < 3000 and t.col1 = tt.min1
    limit 1;

Note the limit 1 so only one row is updated. You can then count the number of modified rows (using mysql_affected_rows() for instance). When it is 0, then stop.

You can also set this up in a MySQL stored procedure, if you want to do the looping in MySQL.

Upvotes: 1

Related Questions