update

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 the SAP ASE 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 Creating and Using Abstract Plans in the Performance and Tuning Series: Query Processing and Abstract Plans for more information.

Examples

• Example 1 – Changes all the McBaddens in the authors table to 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. The SAP ASE 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.

• In pre-12.5.2 versions of SAP ASE, queries that used update and delete on views with a union all clause were sometimes resolved without using worktables, which occasionally lead to incorrect results. In SAP ASE 12.5.2, queries that use update and delete on views with a union all clause are always resolved using worktables in tempdb.

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.

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.

Auditing

Values in event and extrainfo columns of sysaudits are: