UPDATE Statement

For SQL Server 6.5 information, see UPDATE Statement in What's New for SQL Server 6.5.

Changes data in existing rows, either by adding new data or by modifying existing data.

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 sample syntax, 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.

Rules for an Update-In-Place

In SQL Server 6.0, various techniques are used to determine whether or not an UPDATE can be performed "in-place." When an "update-in-place" occurs, the overall performance of the UPDATE statement is improved because the update is direct (only the row modifications to the page are logged). The decision as to whether or not an update-in-place can occur is based on the following requirements. If these requirements are not true, a deferred update will take place. A deferred update is a delete followed by an insert.

• The UPDATE cannot affect the column(s) that participates in a clustered index.
• The table cannot have an UPDATE trigger.
• The table cannot be marked for replication.
• For single-row updates:
  • The updated column(s) can be variable length, but the new total row size must fit on the same page as the old row.
  • The updated column(s) can participate in a nonunique nonclustered index only if the index key is a fixed-width column.
  • The updated column(s) can participate in a unique nonclustered index only if the index key is fixed-width and the WHERE clause criteria must have an exact match (based on an unique index match¾the index could be the same as the updated column).
  • The new row cannot include differing bytes by more than 50 percent of the original row size, and the total number of discontiguous differing bytes is not more than 24.
• For multirow updates:
  • The updated column must be fixed length.
  • The updated column cannot participate in a unique nonclustered index.
  • The updated column can participate in a nonunique nonclustered index only if the column is a fixed-width column (the index used to find rows cannot be the same as the updated column).
  • The table cannot include a column with the timestamp datatype.

To see the update strategy used (DIRECT or DEFERRED), set the SHOWPLAN session setting. For details, see the SET statement.

Transact-SQL Extension for the UPDATE Statement

In addition to the syntax shown earlier, Transact-SQL includes a feature that allows you to select data from a table or tables and update corresponding data in the first named table. The functionality provided is similar to that of using a correlated subquery as a search_condition in the WHERE clause of a standard UPDATE.

Transact-SQL extension 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}]]
[FROM {table_name | view_name}
    [, {table_name | view_name}]...]
        [..., {table_name16 | view_name16}]]
[WHERE clause]

where

FROM

Lets you name more than one table or view to use with a WHERE clause to specify which rows to update. The additional FROM clause allows you to update rows from one table based on data stored in other tables, giving you similar functionality to a correlated subquery (SELECT statement in the WHERE clause).

For an example of the differences between correlated subqueries and this Transact-SQL extension, see the examples later in this section.

Setting Variables and Columns

In earlier releases of SQL Server, it was impossible to verify both the new and old values of a modified column. Values returned from the SELECT statement could not be guaranteed at the time of the UPDATE unless an explicitly defined transaction using HOLDLOCK was defined.

In earlier releases:

BEGIN TRANSACTION
SELECT variable_name = column_name1
    FROM table_name
        WHERE column_name2 = expression
            HOLDLOCK
UPDATE table_name
    SET column_name1 = expression
        WHERE column_name2 = expression
COMMIT TRANSACTION

In SQL Server 6.0:

UPDATE table_name
    SET column_name1 = expression, variable_name = column_name1
        WHERE column_name2 = expression

In this single update (an implicit transaction), the locks are held only until the update completes (no need to hold them for both an UPDATE and a SELECT).

Permission

UPDATE permission defaults to the table owner, who can transfer it to other users.

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

See Also