asked
sql updating rows dynamically based on a value
Hello, if we have table like below: id value1 value2 --------------------- 1 0 0 2 0 0 3 0 0 4 0 0 and a variable as @total. We want to update each row based on "id column" and stop updating when our variable is equal to zero (@total=0). So if we have: UPDATE table SET value1 = @total-10 WHERE id=1 UPDATE table SET value1 = @total-20 WHERE id=2 UPDATE table SET value1 = @total-30 WHERE id=3 UPDATE table SET value1 = @total-30 WHERE id=4 First, how can we say "WHERE id=NEXT ID" from 3rd line onward, and make it dynamic? Second, how can we stop this process when @total=0?
SQL is much more efficient running set-based statements where you act against multiple rows in one batch. To do what you want, you'd have to have a pattern of UPDATE followed or preceded by SET statements to continually update the @total variable and/or a counter variable as you move along. unless ... If there is a relationship between the Id and the amount to subtract from value1, you could do this as a set-based operation using a formula. If, for example, you subtract 10 each time, and the Id values are consecutive starting with 1, you could do this: DECLARE @total int = {starting value}; UPDATE table1 SET value1 = @total - 10 * Id WHERE @total >= 10 * Id ;
18 People are following this question.
