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
ConceptsLimiting 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 ResourcesUPDATE (Transact-SQL)