The SQL command UPDATE changes data in specified columns. The basic syntax is as follows:

UPDATE TableOrView
SET Column&ExpressionList [FROM TableSources]
[WHERE RowSearchCondition]

The UPDATE command changes data in specified columns for all rows of a TableOrView.

The SET clause is a comma-separated list of specified columns to be updated paired with its corresponding new value as per this syntax:

column = expression

The expression can be items such as constants, values from another table or view, or calculated values.

The FROM clause specifies other table or view sources to be utilized in the search condition of the WHERE clause of a UPDATE command, i.e. no rows are updated from TableSources.

The WHERE clause applies the UPDATE command only to the rows that meet the RowSearchCondition.

EGs

UPDATE all rows

Updates specified columns for all rows in a table.

UPDATE tblProducts
SET UnitCost = UnitCost * 1.05, LastUpdate = '07/07/2000'
--A 5% increase in all product costs.

UPDATE specific rows

Updates specified columns for rows that meet a criteria.

UPDATE tblProducts
SET UnitCost = UnitCost * 1.05, LastUpdate = '07/07/2000'
WHERE LastUpdate < '03/18/2000'

UPDATE based on another table

Updates specified column in a specified table that meet a criteria from another table.

UPDATE tblProducts
SET UnitCost = tblNewProducts.Cost
FROM tblNewProducts join tblProducts
    on tblNewProducts.ID=tblProducts.ID
WHERE NewProduct.DateChanged > '03/18/2000'

UPDATE based on multiple joins

An Order has multiple Receipts. Each Order has a Distributor, whose name can be looked up in the Company table. Update the Receipt table with the name of the Distributor.

UPDATE Receipt
SET ReceiptDistributorCompanyName = Company.CompanyName
FROM Orders
    LEFT JOIN Receipt ON Receipt.ReceiptOrderID = Order.OrderID
    LEFT JOIN Distributor ON Distributor.DistributorID = Order.OrderDistributorID
    LEFT JOIN Company ON Company.CompanyID = Distributor.DistributorCompanyID

Page Modified: (Hand noted: 2007-10-12 20:24:12Z) (Auto noted: 2007-11-17 06:45:21Z)