Update fields X times.

velo85

New Member
Is it posible to update some fields X number of times.
Let say I have 100 rows each row have field qty and i want to update that field by -1 150 times total,
so i need procedure to go 1 time each row UPDATE qty = qty - 1, and second time to stop on 50th row
so i got total qty = qty - 150.

150 is variable, and also number of rows can be diferent.

I hope you guys understand what I want to acomplish.

P.S. Sorry for my english. :D
 

Cringer

ProgressTalk.com Moderator
Staff member
Let me try and understand this. You have 100 items in a table that has some sort of qty field. You need to reduce the total qty in the system by x (150 in this case), and you want to spread that as evenly as possible over the 100 rows.

I'm not going to write real code. You need to think about how you will achieve this yourself, but here is some semi-pseudo code.

Code:
updatelimit = 150.
do while updatecounter lt updatelimit:
  for each item no-lock
    where some criteria:
    do for bitem transaction:
      find bitem exclusive-lock 
        where rowid(bitem) eq rowid(item). 
      if available bitem then 
        assign 
          bitem.qty = bitem.qty - 1
          updatecounter = updatecounter + 1.
    end. 
  end. 
end.
Be aware, if anything fails in that loop it will only back out the current record, not the full transaction. If you need the whole transaction rolling back on failure then change the transaction scope. But be aware that for large values of records you could overstep the locktable limit depending on what others are doing.
And it's not the prettiest of solutions either. But I'm presuming this is some sort of one time data update?
 

velo85

New Member
Exectly what I need,
but this do it twice it dont stop on 150. it goes to 200, because second time when start update updatecounter is 100. i need after each update to count updatecounter.


Thank you very much.
 
Last edited:

Cringer

ProgressTalk.com Moderator
Staff member
You're right. You'll need to check the value of updatecounter after each update and do a leave when it's ge updatelimit. Well done for spotting it's wrong, but now it's up to you to work out how to fix it.
 

velo85

New Member
Thankyou , I just add if updatecounter > updatelimit then leave.

So this work fine.
Thankyou again very much.
 

Cringer

ProgressTalk.com Moderator
Staff member
That will probably do 151 updates as variables initialise to 0. Just check. If so, change it to >=.
 
Top