update

Description

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

Syntax

update [[database.]owner.]{table_name | view_name} 
	set [[[database.]owner.]{table_name.|view_name.}]
		column_name1 =
			{expression1 | NULL | (select_statement)} |
		variable_name1 =
			{expression1 | NULL | (select_statement)}
		[, column_name2 = 
			{expression2 | NULL | (select_statement)}]... |
		[, variable_name2 = 
			{expression2 | NULL | (select_statement)}]...

	[from [[database.]owner.]{view_name [readpast]|
		table_name [readpast] 
			[(index {index_name | table_name}
			[ prefetch size ][lru|mru])]}
		[,[[database.]owner.]{view_name [readpast]|
		table_name [readpast] 
			[(index {index_name | table_name }
			[ prefetch size ][lru|mru])]}]
	...]
	[where search_conditions]
	[plan "abstract plan"]

update [[database.]owner.]{table_name | view_name} 
	set [[[database.]owner.]{table_name.|view_name.}]
		column_name1 =
			{expression1 | NULL | (select_statement)} |
		variable_name1 =
			{expression1 | NULL | (select_statement)}
		[, column_name2 = 
			{expression2 | NULL | (select_statement)}]... |
		[, variable_name2 = 
			{expression2 | NULL | (select_statement)}]...
	where current of cursor_name

Parameters

table_name | view_name: is the name of the table or view to update. Specify the database name if the table or view is in another database, and specify the owner’s name if more than one table or view of that name exists in the database. The default value for owner is the current user, and the default value for database is the current database.

set: specifies the column name or variable name and assigns the new value. The value can be an expression or a NULL. When more than one column name or variable name and value are listed, they must be separated by commas.

from: uses data from other tables or views to modify rows in the table or view you are updating.

readpast: causes the update command to modify unlocked rows only on datarows-locked tables, or rows on unlocked pages, for datapages-locked tables. update...readpast silently skips locked rows or pages rather than waiting for the locks to be released.

where: is a standard where clause (see where clause).

index {index_name | table_name}: index_name specifies the index to be used to access table_name. You cannot use this option when you update a view.

prefetch size

specifies the I/O size, in kilobytes, for tables bound to caches with large I/Os configured. You cannot use this option when you update a view. sp_helpcache shows the valid sizes for the cache to which an object is bound or for the default cache. To configure the data cache size, use sp_cacheconfigure.

When using prefetch and designating the prefetch size (size), the minimum is 2K and any power of two on the logical page size up to 16K. prefetch size options in kilobytes are:

Logical page size	Prefetch size options
2	2, 4, 8 16
4	4, 8, 16, 32
8	8, 16, 32, 64
16	16, 32, 64, 128

The prefetch size specified in the query is only a suggestion. To allow the size specification configure the data cache at that size. If you do not configure the data cache to a specific size, the default prefetch size is used.

If Component Integration Services is enabled, you cannot use prefetch for remote servers.

lru | mru: specifies the buffer replacement strategy to use for the table. Use lru to force the optimizer to read the table into the cache on the MRU/LRU (most recently used/least recently used) chain. Use mru to discard the buffer from cache and replace it with the next buffer for the table. You cannot use this option when you update a view.

where current of: causes Adaptive Server to update the row of the table or view indicated by the current cursor position for cursor_name.

index_name: is the name of the index to be updated. If an index name is not specified, the distribution statistics for all the indexes in the specified table are updated.

plan "abstract plan": specifies the abstract plan to use to optimize the query. It can be a full or partial plan, specified in the abstract plan language. See Chapter 30, “Creating and Using Abstract Plans,” in the Performance and Tuning Guide for more information.

Examples

Example 1

All the McBaddens in the authors table are now MacBaddens:

update authors 
set au_lname = "MacBadden" 
where au_lname = "McBadden"

Example 2

Modifies the total_sales column to reflect the most recent sales recorded in the sales and salesdetail tables. This 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 total_sales = total_sales + qty 
from titles, salesdetail, sales 
where titles.title_id = salesdetail.title_id 
    and salesdetail.stor_id = sales.stor_id 
    and salesdetail.ord_num = sales.ord_num 
    and sales.date in 
        (select max(sales.date) from sales)

Example 3

Changes the price of the book in the titles table that is currently pointed to by title_crsr to $24.95:

update titles 
set price = 24.95
where current of title_crsr

Example 4

Finds the row for which the IDENTITY column equals 4 and changes the price of the book to $18.95. Adaptive Server replaces the syb_identity keyword with the name of the IDENTITY column:

update titles 
set price = 18.95
where syb_identity = 4

Example 5

Updates the titles table using a declared variable:

declare @x money
select @x = 0
update titles
    set total_sales = total_sales + 1,
    @x = price
    where title_id = "BU1032"

Example 6

Updates rows on which another task does not hold a lock:

update salesdetail set discount = 40
        from salesdetail readpast
    where title_id like "BU1032"
        and qty > 100

Usage

Use update to change values in rows that have already been inserted. Use insert to add new rows.
You can refer to as many as 15 tables in an update statement.
update interacts with the ignore_dup_key, ignore_dup_row, and allow_dup_row options set with the create index command. See create index for more information.
You can define a trigger that takes a specified action when an update command is issued on a specified table or on a specified column in a table.

Using variables in update statements

You can assign variables in the set clause of an update statement, similarly to setting them in a select statement.
Before you use a variable in an update statement, you must declare the variable using declare, and initialize it with select, as shown in Example 5.
Variable assignment occurs for every qualified row in the update.

When a variable is referenced on the right side of an assignment in an update statement, the current value of the variable changes as each row is updated. The current value is the value of the variable just before the update of the current row. The following example shows how the current value changes as each row is updated.

Suppose you have the following statement:

declare @x int
select @x=0
update table1
    set C1=C1+@x, @x=@x+1
    where column2=xyz

The value of C1 before the update begins is 1. The following table shows how the current value of the @x variable changes after each update:

Row	Initial C1 value	Initial @x value	Calculations: C1+@x= updated C1	Updated C1 value	Calculations: @x+1= updated @x	Updates value
A	1	0	1+0	1	0+1	1
B	1	1	1+1	2	1+1	2
C	2	2	2+2	4	2+1	3
D	4	3	4+3	7	3+1	4

When multiple variable assignments are given in the same update statement, the values assigned to the variables can depend on their order in the assignment list, but they might not always do so. For best results, do not rely on placement to determine the assigned values.
If multiple rows are returned and a nonaggregating assignment of a column to a variable occurs, then the final value of the variable is the last row processed; therefore, it might not be useful.
An update statement that assigns values to variables need not set the value of any qualified row.
If no rows qualify for the update, the variable is not assigned.
A variable that is assigned a value in the update statement cannot be referenced in subquery in that same update statement, regardless of where the subquery appears in that update statement.
A variable that is assigned a value in the update statement cannot be referenced in a where or having clause in that same update statement.
In an update driven by a join, a variable that is assigned a value in the right hand side of the update statement uses columns from the table that is not being updated. The result value depends on the join order chosen for the update and the number of rows that qualify from the joined table.
Updating a variable is not affected by a rollback of the update statement because the value of the updated variable is not stored on disk.

Using update with transactions

When you set chained transaction mode on, and no transaction is currently active, Adaptive Server implicitly begins a transaction with the update statement. To complete the update, you must either commit the transaction or rollback the changes. For example:
```
update stores set city = 'Concord'
    where stor_id = '7066'
if exists (select t1.city, t2.city 
    from stores t1, stores t2 
    where t1.city = t2.city 
    and t1.state = t2.state 
    and t1.stor_id < t2.stor_id)
        rollback transaction
else
        commit transaction
```
This batch begins a transaction (using chained transaction mode) and updates a row in the stores table. If it updates a row containing the same city and state information as another store in the table, it rolls back the changes to the stores table and ends the transaction. Otherwise, it commits the updates and ends the transaction.
Adaptive Server does not prevent you from issuing an update statement that updates a single row more than once in a given transaction. For example, both of these updates affect the price of the book with title_id MC2022, since its type id “mod_cook”:
```
begin transaction
update titles 
set price = price + $10 
where title_id = "MC2222"
update titles
set price = price * 1.1 
where type = "mod_cook"
```

Using joins in updates

Performing joins in the from clause of an update is an Transact-SQL extension to the ANSI standard SQL syntax for updates. Because of the way an update statement is processed, updates from a single statement do not accumulate. That is, if an update statement contains a join, and the other table in the join has more the one matching value in the join column, the second update is not based on the new values from the first update but on the original values. The results are unpredictable, since they depend on the order of processing. Consider this join:
```
update titles set total_sales = total_sales + qty
    from titles t, salesdetail sd
    where t.title_id = sd.title_id
```
The total_sales value is updated only once for each title_id in titles, for one of the matching rows in salesdetail. Depending on the join order for the query, on table partitioning, or on the indexes available, the results can vary each time. But each time, only a single value from salesdetail is added to the total_sales value.

If the intention is to return the sum of the values that match the join column, the following query, using a subquery, returns the correct result:
```
update titles set total_sales = total_sales +
    (select isnull(sum(qty),0)
        from salesdetail sd
        where t.title_id = sd.title_id)
    from titles t
```

Using update with character data

Updating variable-length character data or text columns with the empty string ("") inserts a single space. Fixed-length character columns are padded to the defined length.
All trailing spaces are removed from variable-length column data, except when a string contains only spaces. Strings that contain only spaces are truncated to a single space. Strings longer than the specified length of a char, nchar, unichar, varchar, univarchar, or nvarchar column are silently truncated unless you set string_rtruncation on.
An update to a text column initializes the text column, assigns it a valid text pointer, and allocates at least one text page.

Using update with cursors

To update a row using a cursor, define the cursor with declare cursor, then open it. The cursor name cannot be a Transact-SQL parameter or a local variable. The cursor must be updatable, or Adaptive Server returns an error. Any update to the cursor result set also affects the base table row from which the cursor row is derived.
The table_name or view_name specified with an update...where current of must be the table or view specified in the first from clause of the select statement that defines the cursor. If that from clause references more than one table or view (using a join), you can specify only the table or view being updated.

After the update, the cursor position remains unchanged. You can continue to update the row at that cursor position, provided another SQL statement does not move the position of that cursor.
Adaptive Server allows you to update columns that are not specified in the list of columns of the cursor’s select_statement, but that are part of the tables specified in the select_statement. However, when you specify a column_name_list with for update, and you are declaring the cursor, you can update only those specific columns.

Updating IDENTITY columns

You cannot update a column with the IDENTITY property, either through its base table or through a view. To determine whether a column was defined with the IDENTITY property, use sp_help on the column’s base table.
An IDENTITY column selected into a result table observes the following rules with regard to inheritance of the IDENTITY property:
- If an IDENTITY column is selected more than once, it is defined as NOT NULL in the new table. It does not inherit the IDENTITY property.
- If an IDENTITY column is selected as part of an expression, the resulting column does not inherit the IDENTITY property. It is created as NULL if any column in the expression allows nulls; otherwise, it is NOT NULL.
- If the select statement contains a group by clause or aggregate function, the resulting column does not inherit the IDENTITY property. Columns that include an aggregate of the IDENTITY column are created NULL; others are created NOT NULL.
- An IDENTITY column that is selected into a table with a union or join does not retain the IDENTITY property. If the table contains the union of the IDENTITY column and a NULL column, the new column is defined as NULL. Otherwise, it is defined as NOT NULL.

Updating data through views

You cannot update views defined with the distinct clause.
If a view is created with check option, each row that is updated through the view must remain visible through the view. For example, the stores_cal view includes all rows of the stores table where state has a value of “CA”. The with check option clause checks each update statement against the view’s selection criteria:
```
create view stores_cal
as select * from stores
where state = "CA"
with check option
```
An update statement such as this one fails if it changes state to a value other than “CA”:
```
update stores_cal
set state = "WA"
where store_id = "7066"
```
If a view is created with check option, all views derived from the base view must satisfy the view’s selection criteria. Each row updated through a derived view must remain visible through the base view.

Consider the view stores_cal30, which is derived from stores_cal. The new view includes information about stores in California with payment terms of “Net 30”:
```
create view stores_cal30
as select * from stores_cal
where payterms = "Net 30"
```
Because stores_cal was created with check option, all rows updated through stores_cal30 must remain visible through stores_cal. Any row that changes state to a value other than “CA” is rejected.

Notice that stores_cal30 does not have a with check option clause of its own. Therefore, you can update a row with a payterms value other than “Net 30” through stores_cal30. For example, the following update statement would be successful, even though the row would no longer be visible through stores_cal30:
```
update stores_cal30
set payterms = "Net 60"
where stor_id = "7067"
```
You cannot update a row through a view that joins columns from two or more tables, unless both of the following conditions are true:
- The view has no with check option clause, and
- All columns being updated belong to the same base table.
update statements are allowed on join views that contain a with check option clause. The update fails if any of the affected columns appear in the where clause in an expression that includes columns from more than one table.
If you update a row through a join view, all affected columns must belong to the same base table.

Using index, prefetch, or lru | mru

index, prefetch, and lru | mru override the choices made by the Adaptive Server optimizer. Use them with caution, and always check the performance impact with set statistics io on. For more information about using these options, see the Performance and Tuning Guide.

Using readpast

The readpast option applies only to data-only-locked tables. readpast is ignored if it is specified for an allpages-locked table.
The readpast option is incompatible with the holdlock option. If both are specified in the same select command, an error is generated and the command terminates.
If the session-wide isolation level is 3, the readpast option is ignored.
If the transaction isolation level for a session is 0, update commands using readpast do not issue warning messages. For datapages-locked tables, these commands modify all rows on all pages that are not locked with incompatible locks. For datarows-locked tables, they affect all rows that are not locked with incompatible locks.
If an update command with the readpast option applies to two or more text columns, and the first text column checked has an incompatible lock on it, readpast locking skips the row. If the column does not have an incompatible lock, the command acquires a lock and modifies the column. Then, if any subsequent text column in the row has an incompatible lock on it, the command blocks until it can obtain a lock and modify the column.
For more information on readpast locking, see the Performance and Tuning Guide.

Standards

SQL92 – Compliance level: Entry-level compliant.

The following are Transact-SQL extensions:

The use of a from clause or a qualified table or column name are Transact-SQL extensions detected by the FIPS flagger. Updates through a join view or a view of which the target list contains an expression are Transact-SQL extensions that cannot be detected until run time and are not flagged by the FIPS flagger.
The use of variables.
readpast

Permissions

update permission defaults to the table or view owner, who can transfer it to other users.

If set ansi_permissions is on, you need update permission on the table being updated and, in addition, you must have select permission on all columns appearing in the where clause and on all columns following the set clause. By default, ansi_permissions is off.