how can I Update top 100 records in sql server
Note, the parentheses are required for UPDATE statements:
update top (100) table1 set field1 = 1
Without an ORDER BY
the whole idea of TOP
doesn't make much sense. You need to have a consistent definition of which direction is "up" and which is "down" for the concept of top to be meaningful.
Nonetheless SQL Server allows it but doesn't guarantee a deterministic result.
The UPDATE TOP
syntax in the accepted answer does not support an ORDER BY
clause but it is possible to get deterministic semantics here by using a CTE or derived table to define the desired sort order as below.
;WITH CTE AS
(
SELECT TOP 100 *
FROM T1
ORDER BY F2
)
UPDATE CTE SET F1='foo'