update

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

Examples

Usage

See also sp_bindefault, sp_bindrule, sp_help, sp_helpartition, sp_helpindex, sp_unbindefault, sp_unbindrule in Reference Manual: Procedures.

Standards

ANSI SQL – 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

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.

The following describes permission checks for update that differ based on your granular permissions settings.

SettingDescription
Enabled

With granular permissions enabled, you must be the table or view owner, or a user with update permission..

Disabled

With granular permissions disabled, you must be the table or view owner, a user with update permission, or a user with sa_role.

Auditing

Values in event and extrainfo columns of sysaudits are:

InformationValues
Event

70

Audit option

update

Command or access audited

update to a table

Information in extrainfo
  • 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

InformationValues
Event

71

Audit option

update

Command or access audited

update to a view

Information in extrainfo
  • 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

Related reference
alter table
create default
create index
create rule
create trigger
insert
where clause