Changing Data with UPDATE

The UPDATE statement can change data values in single rows, groups of rows, or all the rows in a table or view. It can also be used to update rows in a remote server using either a linked server name or the OPENROWSET and OPENQUERY functions, as long as the OLE DB provider used to access the remote server supports updates. An UPDATE statement referencing a table or view can change the data in only one base table at a time.

The UPDATE statement has these major clauses:

• SET

  Contains a comma-separated list of the columns to be updated and the new value for each column, in the form column_name = expression. The value supplied by the expressions include items such as constants, values selected from a column in another table or view, or values calculated by a complex expression.

• FROM

  Identifies the tables or views that supply the values for the expressions in the SET clause, and optional join conditions between the source tables or views.

• WHERE

  Specifies the search condition that defines the rows from the source tables and views that qualify to provide values to the expressions in the SET clause.

This update statement increases the prices of all the Northwind products in category 2 by 10 percent:

UPDATE Northwind.dbo.Products

SET UnitPrice = UnitPrice * 1.1

WHERE CategoryID = 2

To change data using UPDATE

Note UPDATE is logged; if you are changing large blocks of text or image data, consider using the UPDATETEXT or WRITETEXT statement, which by default is not logged. For more information, see Adding ntext, text, or image Data to Inserted Rows.