update

Description

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

Syntax

update [top unsigned_integer]
	[[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 
			[(index {index_name | table_name}
				[prefetch size][lru|mru])]}
			[readpast] 
		[,[[database.]owner.]{view_name [readpast] | table_name 
			[(index {index_name | table_name}
				[prefetch size][lru|mru])]}]
			[readpast] ...]
	[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.

top unsigned_integer

inserts the top n clause immediately after the keyword, and limits the number of rows updated.

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 CIS 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


Using variables in update statements


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


Using update with cursors


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:


Updating data through views


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

Standards

ANSI SQL – Compliance level: Entry-level compliant.

The following are Transact-SQL extensions:

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.

Auditing

Values in event and extrainfo columns of sysaudits are:

Event

Audit option

Command or access audited

Information in extrainfo

70

update

update to a table

  • Roles – current active roles

  • Keywords or optionsupdate or writetext

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – original login name, if set proxy is in effect

71

update

update to a view

  • Roles – current active roles

  • Keywords or optionsupdate or writetext

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – original login name, if set proxy is in effect

See also

Commands alter table, create default, create index, create rule, create trigger, insert, where clause

System procedures sp_bindefault, sp_bindrule, sp_help, sp_helpartition, sp_helpindex, sp_unbindefault, sp_unbindrule