Changing Data by Using the FROM Clause
Use the FROM clause to pull data from one or more tables or views into the table you want to update. The following example modifies the
SalesYTD column in the
SalesPerson table to reflect the most recent sales recorded in the
USE AdventureWorks; GO UPDATE Sales.SalesPerson SET SalesYTD = SalesYTD + SubTotal FROM Sales.SalesPerson AS sp JOIN Sales.SalesOrderHeader AS so ON sp.SalesPersonID = so.SalesPersonID AND so.OrderDate = (SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader WHERE SalesPersonID = sp.SalesPersonID); GO
The previous example assumes that only one sale is recorded for a specified salesperson on a specific date and that updates are current. If more than one sale for a specified salesperson can be recorded on the same day, the example shown does not work correctly. The example runs without error, but each
SalesYTD value is updated with only one sale, regardless of how many sales actually occurred on that day. This is because a single UPDATE statement never updates the same row two times.
In the situation in which more than one sale for a specified salesperson can occur on the same day, all the sales for each sales person must be aggregated together within the
UPDATE statement, as shown in the following example.
USE AdventureWorks; GO UPDATE Sales.SalesPerson SET SalesYTD = SalesYTD + (SELECT SUM(so.SubTotal) FROM Sales.SalesOrderHeader AS so WHERE so.OrderDate = (SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader AS so2 WHERE so2.SalesPersonID = so.SalesPersonID) AND Sales.SalesPerson.SalesPersonID = so.SalesPersonID GROUP BY so.SalesPersonID); GO