Changing Data Using the SET Clause

Changing Data 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 given in the SET in all rows that match the WHERE clause search condition. If no WHERE clause is specified, all rows are updated. For example, if all the publishing houses in the publishers table move their head offices to Atlanta, Georgia, this UPDATE statement would be used:

UPDATE publishers SET city = 'Atlanta', state = 'Georgia'

Computed column values can be calculated and used in an update. For example, to double all the prices in the titles table, the price column in the titles table can be set to equal price * 2.

The expressions used in the SET clause can also be subqueries that return only one value; for example, if the Northwind database had an OrderSummary table:

UPDATE OrderSummary
SET Last30Days =
    (SELECT SUM(OrdDet.UnitPrice * OrdDet.Quantity)
     FROM [Order Details] AS OrdDet
          JOIN Orders AS Ord
          ON (OrdDet.OrderID = Ord.OrderID
              AND Ord.OrderDate > DATEADD(dd,-30,GETDATE()) )
    )