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

UPDATE table with identity column

If a MS SQL table has an identity (auto-incremented) column, then you have to take extra steps. In this example we renumber ID 78 to 1908.

set identity_insert MyTbl on;

-- Copy the old row to a new row with the correct ID value:
insert into MyTbl (ID, Foo, Bar)
select 1908, Foo, Bar
from MyTbl where ID = 78;

-- Delete the old row:
delete from MyTbl where ID = 78;

set identity_insert MyTbl off;

Page Modified: (Hand noted: 2007-10-12 20:24:12Z) (Auto noted: 2011-07-27 18:23:01Z)