Tutorial :how can I Update top 100 records in sql server


I want to update the top 100 records in SQL Server. I have a table T1 with fields F1 and F2. T1 has 200 records. I want to update the F1 field in the top 100 records. How can I update based on TOP 100 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'  


for those like me still stuck with SQL Server 2000, SET ROWCOUNT {number}; can be used before the UPDATE query


will limit the update to 100 rows

It has been deprecated at least since SQL 2005, but as of SQL 2017 it still works. https://docs.microsoft.com/en-us/sql/t-sql/statements/set-rowcount-transact-sql?view=sql-server-2017


update tb set  f1=1 where id in (select top 100 id from tb where f1=0)  


What's even cooler is the fact that you can use an inline Table-Valued Function to select which (and how many via TOP) row(s) to update. That is:

UPDATE MyTable  SET Column1=@Value1  FROM tvfSelectLatestRowOfMyTableMatchingCriteria(@Param1,@Param2,@Param3)  

For the table valued function you have something interesting to select the row to update like:

CREATE FUNCTION tvfSelectLatestRowOfMyTableMatchingCriteria  (      @Param1 INT,      @Param2 INT,      @Param3 INT  )  RETURNS TABLE AS RETURN  (      SELECT TOP(1) MyTable.*      FROM MyTable      JOIN MyOtherTable        ON ...      JOIN WhoKnowsWhatElse        ON ...      WHERE MyTable.SomeColumn=@Param1 AND ...      ORDER BY MyTable.SomeDate DESC  )  

..., and there lies (in my humble opinion) the true power of updating only top selected rows deterministically while at the same time simplifying the syntax of the UPDATE statement.


UPDATE Dispatch_Post SET isSync = 1 WHERE ChallanNo IN (SELECT TOP 1000 ChallanNo FROM dbo.Dispatch_Post ORDER BY CreatedDate DESC)

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »