delete

Syntax

delete
	[top unsigned_integer]
	[from] [[database.]owner.]{view_name|table_name}
	[where search_conditions]
	[plan "abstract plan"]

delete [[database.]owner.]{table_name | view_name} 
	[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"]

delete [from] [[database.]owner.]{table_name|view_name}
	where current of cursor_name

Parameters

• from (after delete) – is an optional keyword used for compatibility with other versions of SQL.
• view_name | table_name – is the name of the view or table from which to remove rows. Specify the database name if the view or table is in another database, and specify the owner’s name if more than one view or table 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.
• where – is a standard where clause.
• from (after table_name or view_name) – lets you name more than one table or view to use with a where clause when specifying which rows to delete. This from clause allows you to delete rows from one table based on data stored in other tables, giving you much of the power of an embedded select statement.
• top unsigned_integer – is used to limit the number of rows to the number of rows specified by unsigned_integer.
• readpast – specifies that the delete command skip all pages or rows on which incompatible locks are held, without waiting for locks or timing out. For datapages-locked tables, readpast skips all rows on pages on which incompatible locks are held; for datarows-locked tables, it skips all rows on which incompatible locks are held.
• index index_name – specifies an index to use for accessing table_name. You cannot use this option when you delete from a view.
• prefetch size – specifies the I/O size, in kilobytes, for tables that are bound to caches with large I/Os configured. You cannot use prefetch when you delete from a view. sp_helpcache shows the valid sizes for the cache an object is bound to, or for the default cache.

  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.

  To configure the data cache size, use sp_cacheconfigure.

  Note: You cannot use prefetch for remote servers if you enable CIS.
• 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 delete from a view.
• 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 Guide: Optimizer and Abstract Plans.
• where current of cursor_name – causes the SAP ASE server to delete the row of the table or view indicated by the current cursor position for cursor_name.

Examples

• Example 1 – Deletes all rows from the authors table:

  delete authors

• Example 2 – Deletes a row or rows from the authors table:

  delete from authors where au_lname = "McBadden"

• Example 3 – Deletes rows for books written by Bennet from the titles table.

  delete titles 
  from titles, authors, titleauthor 
  where authors.au_lname = 'Bennet' 
    and authors.au_id = titleauthor.au_id 
    and titleauthor.title_id = titles.title_id

  The pubs2 database includes a trigger (deltitle) that prevents the deletion of the titles recorded in the sales table; drop this trigger for this example to work.

• Example 4 – Deletes a row from the titles table currently indicated by the cursor title_crsr:

  delete titles where current of title_crsr

• Example 5 – Determines which row has a value of 4 for the IDENTITY column and deletes it from the authors table. Note the use of the syb_identity keyword instead of the actual name of the IDENTITY column:

  delete authors where syb_identity = 4

• Example 6 – Deletes rows from authors, skipping any locked rows:

  delete from authors from authors readpast 
  where state = "CA"

• Example 7 – Deletes rows from stores, skipping any locked rows. If any rows in authors are locked, the query blocks on these rows, waiting for the locks to be released:

  delete stores from stores readpast, authors
  where stores.city = authors.city

Usage

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 and later, queries that use update and delete on views with a union all clause are always resolved using worktables in tempdb.

The index, prefetch, and lru | mru options override the choices made by the SAP ASE optimizer. Use these options with caution, and always check the performance impact with set statistics io on. See Using the set statistics Command in Performance and Tuning Guide: Monitoring and Analyzing.

You can define a trigger to take a specified action when a delete command is issued on a specified table.

Standards

ANSI SQL – Compliance level: Entry-level compliant. The use of more than one table in the from clause and qualification of table name with database name are Transact-SQL extensions.

readpast is a Transact-SQL extension.

Permissions

If set ansi_permissions is on, you must have select permission on all columns appearing in the where clause, in addition to the regular permissions required for delete statements. By default, ansi_permissions is off.

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

Auditing

Values in event and extrainfo columns of sysaudits are: