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



Question:

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?


Solution:1

Note, the parentheses are required for UPDATE statements:

update top (100) table1 set field1 = 1  


Solution:2

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'  


Solution:3

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

SET ROWCOUNT 100;  UPDATE Table SET ..;  SET ROWCOUNT 0;  

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


Solution:4

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


Solution:5

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.


Solution:6

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
Previous
Next Post »