Limiting Updated Data by Using TOP
You can use the TOP clause to limit the number of rows that are modified in an UPDATE statement. When a TOP (n) clause is used with UPDATE, the update operation will be performed on a random selection of 'n' number of rows. For example, suppose you want to relieve the sales burden of one of your senior sales persons by assigning some customers to a junior sales person. The following query assigns a random sample of 10 customers from one salesperson to another.
USE AdventureWorks;
UPDATE TOP (10) Sales.Store
SET SalesPersonID = 276
WHERE SalesPersonID = 275;
GO
If you have to use TOP to apply updates in a meaningful chronology, you must use TOP together with ORDER BY in a subselect statement. The following example updates the vacation hours of the 10 employees with the earliest hire dates.
UPDATE HumanResources.Employee
SET VacationHours = VacationHours + 8
FROM (SELECT TOP 10 EmployeeID FROM HumanResources.Employee
ORDER BY HireDate ASC) AS th
WHERE HumanResources.Employee.EmployeeID = th.EmployeeID;
GO
See Also
Concepts
Limiting Result Sets by Using TOP and PERCENT
Changing Data by Using the WHERE Clause
Changing Data by Using the FROM Clause
Changing Data by Using the SET Clause
Changing Data by Using UPDATE
Other Resources
UPDATE (Transact-SQL)
SELECT (Transact-SQL)
TOP (Transact-SQL)