UPDATE








Syntax
 

UPDATE {table_name | view_name}
SET [{table_name | view_name}]
     {column_list
| variable_list
| variable_and_column_list}
	[, {column_list2
		| variable_list2
		| variable_and_column_list2}
		...	[, {column_listN
				| variable_listN
				| variable_and_column_listN}]]

 

[WHERE clause]
 
where
table_name | view_name =
[[database_name.]owner.]{table_name | view_name}




 

 Specifies the name of the table or view used in the UPDATE statement.
 If the table or view is not in the current database, use a fully
 qualified table_name or view_name (database_name.owner.object_name).
 

SET

 Is a required keyword used to introduce the list of column or variable
 clauses to be updated. When more than one column name and value pair
 are listed, separate the names with commas.
 

column_list =
column_name = {expression | DEFAULT | NULL}
 
variable_list =
variable_name = {expression | NULL}
 
column_name

 Specifies a column from the table (table_name) or view (view_name).
   expression
 Is a column_name, constant, function (aggregate functions are not
   allowed), or any combination of column_names, constants, and functions
   connected by an operator(s), or a subquery. For details, see the
 Expressions topic.
 

DEFAULT

 Inserts the default value for that column. For a column defined with
 the timestamp datatype, the next value will be inserted. If a default
 does not exist for the column and the column allows NULLs, NULL will
 be inserted.
 
 DEFAULT is not valid for an identity column; columns with the IDENTITY
 property should not be
 explicitly updated. For more information, see the CREATE TABLE statement.
 

variable_and_column_list =
variable_name = column_name = {expression | NULL}

 Sets local variables as well as columns. This syntax provides the same
 functionality as an UPDATE statement followed by a SELECT statement in
 an explicitly defined transaction. Because the combination of selecting
 and updating is performed during a single UPDATE statement, the
 transaction is implicit and locks are held for a shorter period of time.
 For samplesyntax, see "Setting Variables and Columns," later in
 this topic.
 

WHERE clause =
WHERE {search_conditions | CURRENT OF cursor_name}

 Is used to perform a searched update (using search_conditions) or a
 positioned update (using CURRENT OF cursor_name). When no WHERE clause
 is given in the UPDATE statement, all rows in the table are modified.
 

search_conditions

 Specifies the criteria for a searched update. A search_condition can
 include an expression, a subquery, a constant, and so on. For details
 on valid search_conditions, see the Search Conditions topic.
 
 The IDENTITYCOL keyword can be used in the place of a column_name that
 has the IDENTITY property. For more information, see the CREATE TABLE
 statement.
 

CURRENT OF cursor_name

 Specifies the criteria for a positioned update against the current row
 within the specified cursor_name. Cursor operations affect only the
 single row on which the cursor is positioned. For details about cursors,
 see the Cursors topic.







 

Remarks:

   Use the UPDATE statement to change single rows, groups of rows, or all
   rows in a table. UPDATE specifies which row(s) to change and provides
   the new data. When updating rows, these rules apply:
 
 - Updating a column with an empty string (' ') into a varchar or
   text column inserts a single space. All char columns are right-padded
   to the defined length. All trailing spaces are removed from data
   inserted into varchar columns, except in strings that contain only
   spaces. These strings are truncated to a single space.
 
 - If a column being updated violates a constraint or rule, or if it
   is the wrong datatype, the update does not take place and an error
   message is returned. Constraints are defined with the CREATE TABLE
   or ALTER TABLE statement. Rules are created with the CREATE RULE
   statement and bound with the sp_bindrule system stored procedure.
 
 - Modifying a text column with UPDATE initializes it, assigns a valid
   text pointer to it, and allocates at least one 2K data page (even if
   updating the column with NULL). For details about updating text or
   image fields, see the Text and Image Manipulation topic.

Note:

   The UPDATE statement is logged; if you are replacing or modifying
   large blocks of text or image data, use the WRITETEXT or UPDATETEXT
   statement instead of the UPDATE statement. The WRITETEXT and
   UPDATETEXT statements (by default) are not logged. For details,
   see the Text and Image Manipulation topic.
 
   An UPDATE statement must follow the rules for a batch.
   For details, see the Batches topic.
   

Examples:
A.	UPDATE Statement with Only the SET Clause
 

These examples show how all rows can be affected if a WHERE clause is
eliminated from an UPDATE statement.
 
In this example, if all the publishing houses in the publishers table
move their head offices to Atlanta, Georgia, this is how the publishers
table could be updated:
 

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

This example changes the names of all the publishers to NULL: 
 

           UPDATE publishers
             SET pub_name = NULL

You can also use computed column values in an update. This example
doubles all prices in the titles table:
 

           UPDATE titles
             SET price = price * 2

B.	UPDATE Statement with a WHERE Clause

The WHERE clause specifies which rows are to be updated. For example,
in the unlikely event that northern California is renamed Pacifica
(abbreviated PC) and the people of Oakland vote to change the name of
their city to Bay City, here is how to update the authors table for
all former Oakland residents whose addresses are now out of date:
 

           UPDATE authors
	     SET state = 'PC', city = 'Bay City'
	       WHERE state = 'CA' AND city = 'Oakland'

You must write another statement to change the name of the state
for residents of other northern California cities.
 

C.	UPDATE Statement with a Nested SELECT Statement

To modify the ytd_sales column to reflect the most recent sales recorded
in the sales table, this example assumes that only one set of sales is
recorded for a given title on a given date and that updates are current.
 

           UPDATE titles
	     SET ytd_sales = ytd_sales  qty
	       FROM titles, sales
	   	 WHERE titles.title_id = sales.title_id
		   AND sales.date = (SELECT MAX(sales.date) FROM sales)

This example assumes that only one set of sales is recorded for a given
title on a given date and that updates are current. If this is not the
case (if more than one sale for a given title can be recorded on the
same day), then the example shown here does not work correctly. It
executes without error, but each title is updated with only one sale,
regardless of how many sales actually occurred on that day. This is
because a single UPDATE statement never updates the same row twice.
 
In the situation where more than one sale for a given title can occur
on the same day, all the sales for each title must be aggregated
together within the UPDATE statement, as shown in this example:
 

            UPDATE titles
	      SET ytd_sales =
		(select sum(qty)
	           FROM sales
		     WHERE sales.title_id = titles.title_id
			AND sales.date IN (SELECT MAX(date) FROM sales))
	             FROM titles, sales