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?
This question is related to
sql
sql-server
tsql
sql-update
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 tb set f1=1 where id in (select top 100 id from tb where f1=0)
Try:
UPDATE Dispatch_Post
SET isSync = 1
WHERE ChallanNo
IN (SELECT TOP 1000 ChallanNo FROM dbo.Dispatch_Post ORDER BY
CreatedDate DESC)
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
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
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;
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'
Source: Stackoverflow.com