SQLTeam.com | Weblogs | Forums

Update values based on count


#1

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?


#2

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
FROM MyTable AS U
WHERE ...

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.