It is a common requirement in SQL server development to update top N records in SQL server.In this blog we will see two approaches to accomplish the same.
1) Using UPDATE with TOP
UPDATE TOP (100) Table1 SET field1 = 1
However without an ORDER BY the whole idea of TOP is vague, since there's no way to guarantee WHICH top N rows we are updating.
2) Using CTE
;WITH CTE AS
(
SELECT TOP 100 *
FROM Table1
ORDER BY F2
)
UPDATE CTE SET field1=1
In the above query we are using the ORDER BY on column F2 before updating. So now we have control on which N rows we are updating.
0 Comment(s)