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


Using variables in update statements


Using update with transactions


Using joins in updates


Using update with character data


Using update with cursors


Updating IDENTITY columns


Updating data through views


Using index, prefetch, or lru | mru


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