delete

Removes rows from a table.

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

Examples

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.

SettingDescription
Enabled

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

Disabled

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

Auditing

Values in event and extrainfo columns of sysaudits are:

InformationValues
Event

18

Audit option

delete

Command or access audited

delete from a table

Information in extrainfo
  • Roles – current active roles

  • Keywords or optionsdelete

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

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

InformationValues
Event

19

Audit option

delete

Command or access audited

delete from a view

Information in extrainfo
  • Roles – current active roles

  • Keywords or optionsdelete

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

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

Related reference
create trigger
drop table
drop trigger
truncate table
where clause
update
insert
delete
commit
rollback
declare cursor
open
select
fetch