How do I write this query to update a column based on the case statement?
x
UPDATE Table
SET Form = CASE
WHEN (cnt - rownum < cnt % NumberUp)
THEN 0
ELSE floor((rowNum - 1) / numberUp) + 1
END AS form
ORDER BY Quantity
Advertisement
Answer
As commented, you query looks pretty good, apart from these:
the
ORDER BY
clause does not make sense; anUPDATE
query does not return any record, so ordering is out of scopefor the same reason, you don’t have to alias the updated column
Consider:
UPDATE Table
SET Form = CASE
WHEN (cnt - rownum < cnt % NumberUp) THEN 0
ELSE floor((rowNum - 1) / numberUp) + 1
END