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'
update tb set f1=1 where id in (select top 100 id from tb where f1=0)