Specifying Columns by Using the SET Clause

SET specifies the columns to be changed and the new values for the columns. The values in the specified columns are updated with the values specified in the SET in all rows that match the WHERE clause search condition.

The following example changes the postal code value for rows that match a specified city.

USE AdventureWorks;
GO
UPDATE Person.Address
SET PostalCode = '98000'
WHERE City = 'Bothell';
GO

If no WHERE clause is specified, all rows are updated. For example, this statement updates the values in the Bonus, CommissionPct, and SalesQuota columns for all rows in the SalesPerson table.

USE AdventureWorks;
GO
UPDATE Sales.SalesPerson
SET Bonus = 6000, CommissionPct = .10, SalesQuota = NULL;
GO

Computed column values can be calculated and used in an update operation. The following example doubles the value in the ListPrice column for all rows in the Product table.

USE AdventureWorks ;
GO
UPDATE Production.Product
SET ListPrice = ListPrice * 2;
GO

The expressions used in the SET clause can also be subqueries that return only one value. The following example modifies the SalesYTD column in the SalesPerson table to reflect the most recent sales recorded in the SalesOrderHeader table. The subqueries aggregate the sales for each sales person within the UPDATE statement.

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