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?
9 Answers
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
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
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.
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.