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

User · Accepted Answer

Note  the parentheses are required for UPDATE statements   update top  100  table1 set field1   1

User · Answer

this piece of code can do its job UPDATE TOP  100  table name set column name   value   If you want to show the last 100 records  you can use this if you need  With OrnekWith as   Select Top 100    from table name Order By ID desc   Update table name Set column name   value

User · Answer

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

User · Answer

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

User · Answer

You can also update from select using alias and join   UPDATE  TOP  500  T SET     T SomeColumn    Value  FROM    SomeTable T         INNER JOIN OtherTable O ON O OtherTableFK   T SomeTablePK WHERE   T SomeOtherColumn   1

User · Answer

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

User · Answer

Try   UPDATE Dispatch Post SET isSync   1 WHERE ChallanNo  IN  SELECT TOP 1000 ChallanNo FROM dbo Dispatch Post ORDER BY  CreatedDate DESC

User · Answer

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

[sql] how can I Update top 100 records in sql server

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to sql-update