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