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?

Answer 1

Note, the parentheses are required for UPDATE statements:

update top (100) table1 set field1 = 1

Answer 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'

Answer 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://learn.microsoft.com/en-us/sql/t-sql/statements/set-rowcount-transact-sql?view=sql-server-2017

Answer 4

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

Answer 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.

Answer 6

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

Answer 7

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;

Answer 8

Try:

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

Answer 9

The TOP qualifier can also be used as limit the the number of rows manually updated incorrectly.

Consider the following UPDATE syntax.

UPDATE TOP (1) table1 SET column1 = 0 WHERE column_pk = '123'

Without the TOP clause, if you are doing a manual update and your mouse text selection only selects from "UPDATE" to just before the "WHERE" clause, then the update is applied to ALL rows. With the TOP clause, only one row would get the undesired update.

The TOP constraint can limit the damage of a missing or incorrect WHERE clause or ORDER BY clause. This can be helpful when it is known that only one or a few rows should be updated.