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
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
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
The WHERE clause applies the UPDATE command only to the rows that meet the
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;