SQLTeam.com | Weblogs | Forums

Update values based on count


I have a table with over 2,000 records. I need to be able to do a count on the different values in column 'RevID' and update the column (based on filters) to ensure all values in the RevID is equal to 125 (ok to be a few numbers off)

For example, the count of RevID = "A2" is = 155 and the count of RevID = 'B2' is = 55.

What is the best approach to update "A2" with the value of "B2" until they are both close to the ideal count of 125??

Would a cursor be the best approach?


If you figure out exactly how many rows you need to change (i.e. subtract one count from the other) then you could perhaps do

UPDATE TOP (@RowCountToChange) U

I don't know if you can "control" the sequence in which rows are included for UPDATE by using an ORDER BY - but I suspect that you can.