Removes rows from a table.
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
is an optional keyword used for compatibility with other versions of SQL.
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.
is a standard where clause. See where clause for more information.
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.
is used to limit the number of rows to the number of rows specified by unsigned_integer.
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.
specifies an index to use for accessing table_name. You cannot use this option when you delete from a view.
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.
You cannot use prefetch for remote servers if you enable CIS.
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.
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 16, “Creating and Using Abstract Plans,” in the Performance and Tuning Guide: Optimizer and Abstract Plans.
causes Adaptive Server to delete the row of the table or view indicated by the current cursor position for cursor_name.
Deletes all rows from the authors table:
delete authors
Deletes a row or rows from the authors table:
delete from authors where au_lname = "McBadden"
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.
Deletes a row from the titles table currently indicated by the cursor title_crsr:
delete titles where current of title_crsr
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
Deletes rows from authors, skipping any locked rows:
delete from authors from authors readpast where state = "CA"
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
You can refer to as many as 15 tables in a delete statement.
In pre-12.5.2 versions of Adaptive Server, 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 Adaptive Server 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.
You cannot use delete with a multitable view (one whose from clause names more than one table), even though you may be able to use update or insert on that same view. Deleting a row through a multitable view changes multiple tables, which is not permitted. insert and update statements that affect only one base table of the view are permitted.
Adaptive Server treats two different designations for the same table in a delete as two tables. For example, the following delete issued in pubs2 specifies discounts as two tables (discounts and pubs2..discounts):
delete discounts from pubs2..discounts, pubs2..stores where pubs2..discounts.stor_id = pubs2..stores.stor_id
In this case, the join does not include discounts, so the where condition remains true for every row; Adaptive Server deletes all rows in discounts (which is not the desired result). To avoid this problem, use the same designation for a table throughout the statement.
If you are deleting a row from a table that is referenced from other tables via referential constraints, Adaptive Server checks all the referencing tables before permitting the delete. If the row you are attempting to delete contains a primary key that is being used as a foreign key by one of the referencing tables, the delete is not allowed.
If you do not use a where clause, all rows in the table named after delete [from] are removed. The table, though empty of data, continues to exist until you issue a drop table command.
truncate table and delete without a row specification are functionally equivalent, but truncate table is faster. delete removes rows one at a time and logs these transactions. truncate table removes entire data pages, and the rows are not logged.
Both delete and truncate table reclaim the space occupied by the data and its associated indexes.
You cannot use the truncate table command on a partitioned table. To remove all rows from a partitioned table, either use the delete command without a where clause, or unpartition the table before issuing truncate table.
In chained transaction mode, each delete statement implicitly begins a new transaction if no transaction is currently active. Use commit to complete any deletes, or use rollback to undo the changes. For example:
delete from sales where date < ’01/01/06’ if exists (select stor_id from stores where stor_id not in (select stor_id from sales)) rollback transaction else commit transaction
This batch begins a transaction (using the chained transaction mode) and deletes rows with dates earlier than Jan. 1, 2006 from the sales table. If it deletes all sales entries associated with a store, it rolls back all the changes to sales and ends the transaction. Otherwise, it commits the deletions and ends the transaction. For more information about the chained mode, see the Transact-SQL Users Guide.
You can define a trigger to take a specified action when a delete command is issued on a specified table.
Use the clause where current of with cursors. Before deleting rows using the clause where current of, first define the cursor with declare cursor and open it using the open statement. Use one or more fetch statements to position the cursor on the row to delete. The cursor name cannot be a Transact-SQL parameter or local variable. The cursor must be an updatable cursor, or Adaptive Server returns an error. Any deletion to the cursor result set also affects the base table row from which the cursor row is derived. You can delete only one row at a time using the cursor.
You cannot delete rows in a cursor result set if the cursor’s select statement contains a join clause, even though the cursor is considered updatable. The table_name or view_name specified with a delete...where current of must be the table or view specified in the first from clause of the select statement that defines the cursor.
After the deletion of a row from the cursor’s result set, the cursor is positioned before the next row in the cursor’s result set. You must issue a fetch to access the next row. If the deleted row is the last row of the cursor result set, the cursor is positioned after the last row of the result set. The following describes the position and behavior of open cursors affected by a delete:
If a client deletes a row (using another cursor or a regular delete) and that row represents the current cursor position of other opened cursors owned by the same client, the position of each affected cursor is implicitly set to precede the next available row. However, one client cannot delete a row representing the current cursor position of another client’s cursor.
If a client deletes a row that represents the current cursor position of another cursor defined by a join operation and owned by the same client, Adaptive Server accepts the delete statement. However, it implicitly closes the cursor defined by the join.
readpast allows delete commands on data-only-locked tables to proceed without being blocked by incompatible locks held by other tasks.
On datarows-locked tables, readpast skips all rows on which shared, update, or exclusive locks are held by another task.
On datapages-locked tables, readpast skips all pages on which shared, update, or exclusive locks are held by another task.
Commands specifying readpast block if there is an exclusive table lock.
If the readpast option is specified for an allpages-locked table, it is ignored. The command blocks as soon as it finds an incompatible lock.
If the session-wide isolation level is 3, the readpast option is silently ignored. The command executes at level 3. The command blocks on any rows or pages with incompatible locks.
If the transaction isolation level for a session is 0, a delete command using readpast does not issue warning messages. For datapages-locked tables, delete with readpast modifies all rows on all pages that are not locked with incompatible locks. For datarows-locked tables, it affects all rows that are not locked with incompatible locks.
If the delete command applies to a row with two or more text columns, and any text column has an incompatible lock on it, readpast locking skips the row.
The index, prefetch, and lru | mru options override the choices made by the Adaptive Server optimizer. Use these options with caution, and always check the performance impact with set statistics io on. See Chapter 4, “Using the set statistics command,” in Performance and Tuning Guide: Monitoring and Analyzing.
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.
delete permission defaults to the table or view owner, who can transfer it to other users.
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.
Values in event and extrainfo columns of sysaudits are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
---|---|---|---|
18 |
delete |
delete from a table |
|
19 |
delete |
delete from a view |
|
Commands create trigger, drop table, drop trigger, truncate table, where clause