DELETE

Removes rows from a table.

Syntax

DELETE
    [ FROM ]
        { table_name WITH ( < table_hint_limited > [ ...n ] )
         | view_name
         | rowset_function_limited
        }

        [ FROM { < table_source > } [ ,...n ] ]

    [ WHERE
        { < search_condition >
        | { [ CURRENT OF
                { { [ GLOBAL ] cursor_name }
                    | cursor_variable_name
                }
            ] }
        }
    ]
    [ OPTION ( < query_hint > [ ,...n ] ) ]

< table_source > ::=
    table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ]
    | view_name [ [ AS ] table_alias ]
    | rowset_function [ [ AS ] table_alias ]
    | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
    | < joined_table >

< joined_table > ::=
    < table_source > < join_type > < table_source > ON < search_condition >
    | < table_source > CROSS JOIN < table_source >
    | < joined_table >

< join_type > ::=
    [ INNER | { { LEFT | RIGHT | FULL } [OUTER] } ]
    [ < join_hint > ]
    JOIN

< table_hint_limited > ::=
    { FASTFIRSTROW
        | HOLDLOCK
        | PAGLOCK
        | READCOMMITTED
        | REPEATABLEREAD
        | ROWLOCK
        | SERIALIZABLE
        | TABLOCK
        | TABLOCKX
        | UPDLOCK
    }

< table_hint > ::=
    { INDEX ( index_val [ ,...n ] )
        | FASTFIRSTROW
        | HOLDLOCK
        | NOLOCK
        | PAGLOCK
        | READCOMMITTED
        | READPAST
        | READUNCOMMITTED
        | REPEATABLEREAD
        | ROWLOCK
        | SERIALIZABLE
        | TABLOCK
        | TABLOCKX
        | UPDLOCK
    }

< query_hint > ::=
    { { HASH | ORDER } GROUP
        | { CONCAT | HASH | MERGE } UNION
        | FAST number_rows
        | FORCE ORDER
        | MAXDOP
        | ROBUST PLAN
        | KEEP PLAN
    }

Arguments

FROM

Is an optional keyword that can be used between the DELETE keyword and the target table_name, view_name, or rowset_function_limited.

table_name

Is the name of the table from which the rows are to be removed.

A table variable, within its scope, or a four-part table name (or view name) using the OPENDATASOURCE function as the server name also may be used as a table source in a DELETE statement.

WITH (<table_hint_limited> [...n])

Specifies one or more table hints that are allowed for a target table. The WITH keyword and the parentheses are required. READPAST, NOLOCK, and READUNCOMMITTED are not allowed. For more information about table hints, see FROM.

view_name

Is the name of a view. The view referenced by view_name must be updatable and reference exactly one base table in the FROM clause of the view. For more information about updatable views, see CREATE VIEW.

Note  If the table or view exists in another database or has an owner other than the current user, use a four-part qualified name in the format server_name.database.[owner].object_name. For more information, see Transact-SQL Syntax Conventions.

rowset_function_limited

Is either the OPENQUERY or OPENROWSET function, subject to provider capabilities. For more information about capabilities needed by the provider, see UPDATE and DELETE Requirements for OLE DB Providers. For more information about the rowset functions, see OPENQUERY and OPENROWSET.

FROM <table_source>

Specifies an additional FROM clause. This Transact-SQL extension to DELETE allows you to specify data from <table_sources> and delete corresponding rows from the table in the first FROM clause.

This extension, specifying a join, can be used instead of a subquery in the WHERE clause to identify rows to be removed.

table_name [[AS] table_alias ]

Is the name of the table to provide criteria values for the delete operation.

view_name [[AS] table_alias ]

Is the name of the view to provide criteria values for the delete operation. A view with INSTEAD OF UPDATE trigger cannot be a target of an UPDATE with a FROM clause.

WITH (<table_hint>

Specifies one or more table hints. For more information about table hints, see FROM.

rowset_function [ [AS] table_alias ]

Is the name of a rowset function and an optional alias. For more information about a list of rowset functions, see Rowset Functions.

derived_table [AS] table_alias

Is a subquery that retrieves rows from the database. derived_table is used as input to the outer query.

column_alias

Is an optional alias to replace a column name in the result set. Include one column alias for each column in the select list, and enclose the entire list of column aliases in parentheses.

<joined_table>

Is a result set that is the product of two or more tables, for example:

SELECT *
FROM tab1 LEFT OUTER JOIN tab2 ON tab1.c3 = tab2.c3
    RIGHT OUTER JOIN tab3 LEFT OUTER JOIN tab4
        ON tab3.c1 = tab4.c1
        ON tab2.c3 = tab4.c3

For multiple CROSS joins, use parentheses to change the natural order of the joins.

<join_type>

Specifies the type of join operation.

INNER

Specifies all matching pairs of rows are returned. Discards unmatched rows from both tables. This is the default if no join type is specified.

LEFT [OUTER]

Specifies that all rows from the left table not meeting the specified condition are included in the result set, and output columns from the right table are set to NULL in addition to all rows returned by the inner join.

RIGHT [OUTER]

Specifies that all rows from the right table not meeting the specified condition are included in the result set, and output columns from the left table are set to NULL in addition to all rows returned by the inner join.

FULL [OUTER]

If a row from either the left or right table does not match the selection criteria, specifies the row be included in the result set, and output columns that correspond to the other table be set to NULL. This is in addition to all rows usually returned by the inner join.

JOIN

Is a keyword to indicate that an SQL-92 style join be used in the delete operation.

ON <search_condition>

Specifies the condition on which the join is based. The condition can specify any predicate, although columns and comparison operators are often used, for example:

FROM Suppliers JOIN Products 
    ON (Suppliers.SupplierID = Products.SupplierID)

When the condition specifies columns, they need not have the same name or same data type; however, if the data types are not identical, they must be either compatible or types that Microsoft® SQL Server™ can implicitly convert. If the data types cannot be implicitly converted, the condition must explicitly convert the data type using the CAST function.

For more information about search conditions and predicates, see Search Condition.

CROSS JOIN

Specifies the cross-product of two tables. Returns the same rows as if no WHERE clause was specified in an old-style, non-SQL-92-style join.

WHERE

Specifies the conditions used to limit the number of rows that are deleted. If a WHERE clause is not supplied, DELETE removes all the rows from the table. There are two forms of delete operations based on what is specified in the WHERE clause:

• Searched deletes specify a search condition to qualify the rows to delete.
  
  
• Positioned deletes use the CURRENT OF clause to specify a cursor. The delete operation occurs at the current position of the cursor. This can be more accurate than a searched DELETE that uses a WHERE search_condition clause to qualify the rows to be deleted. A searched DELETE deletes multiple rows if the search condition does not uniquely identify a single row.

<search_condition>

Specifies the restricting conditions for the rows to be deleted. There is no limit to the number of predicates that can be included in a search condition. For more information, see Search Condition.

CURRENT OF

Specifies that the DELETE is done at the current position of the specified cursor.

GLOBAL

Specifies that cursor_name refers to a global cursor.

cursor_name

Is the name of the open cursor from which the fetch is made. If both a global and a local cursor with the name cursor_name exist, this argument refers to the global cursor if GLOBAL is specified, and to the local cursor otherwise. The cursor must allow updates.

cursor_variable_name

Is the name of a cursor variable. The cursor variable must reference a cursor that allows updates.

OPTION (<query_hint> [,...n] )

Are keywords indicating that optimizer hints are used to customize SQL Server's processing of the statement.

{HASH | ORDER} GROUP

Specifies that the aggregations specified in the GROUP BY or COMPUTE clause of the query should use hashing or ordering.

{MERGE | HASH | CONCAT} UNION

Specifies that all UNION operations should be performed by merging, hashing, or concatenating UNION sets. If more than one UNION hint is specified, the query optimizer selects the least expensive strategy from those hints specified.

Note  If a <joint_hint> is also specified for any particular pair of joined tables in the FROM clause, it takes precedence over any <join_hint> specified in the OPTION clause.

FAST number_rows

Specifies that the query is optimized for fast retrieval of the first number_rows (a nonnegative integer). After the first number_rows are returned, the query continues execution and produces its full result set.

FORCE ORDER

Specifies that the join order indicated by the query syntax is preserved during query optimization.

MAXDOP number

Overrides the max degree of parallelism configuration option (of sp_configure) only for the query specifying this option. All semantic rules used with max degree of parallelism configuration option are applicable when using the MAXDOP query hint. For more information, see max degree of parallelism Option.

ROBUST PLAN

Forces the query optimizer to attempt a plan that works for the maximum potential row size at the expense of performance. If such a plan is not possible, the query optimizer returns an error rather than deferring error detection to query execution. Rows may contain variable-length columns; SQL Server allows rows to be defined that have a maximum potential size beyond the ability of SQL Server to process them. Usually, despite the maximum potential size, an application stores rows that have actual sizes within the limits that SQL Server can process. If SQL Server encounters a row that is too long, an execution error is returned.

KEEP PLAN

Forces the query optimizer to relax the estimated recompile threshold for a query. The estimated recompile threshold is the point at which a query is automatically recompiled when the estimated number of indexed column changes (update, delete or insert) have been made to a table. Specifying KEEP PLAN ensures that a query will not be recompiled as frequently when there are multiple updates to a table.

Remarks

DELETE may be used in the body of a user-defined function if the object modified is a table variable.

A four-part table name (or view name) using the OPENDATASOURCE function as the server name may be used as a table source in all places a table name can appear.

The DELETE statement may fail if it violates a trigger or attempts to remove a row referenced by data in another table with a FOREIGN KEY constraint. If the DELETE removes multiple rows, and any one of the removed rows violates a trigger or constraint, the statement is canceled, an error is returned, and no rows are removed.

When an INSTEAD-OF trigger is defined on DELETE actions against a table or view, the trigger executes instead of the DELETE statement. Earlier versions of SQL Server only support AFTER triggers on DELETE and other data modification statements.

When a DELETE statement encounters an arithmetic error (overflow, divide by zero, or a domain error) occurring during expression evaluation, SQL Server handles these errors as if SET ARITHABORT is ON. The remainder of the batch is canceled, and an error message is returned.

The setting of the SET ROWCOUNT option is ignored for DELETE statements against remote tables and local and remote partitioned views.

If you want to delete all the rows in a table, TRUNCATE TABLE is faster than DELETE. DELETE physically removes rows one at a time and records each deleted row in the transaction log. TRUNCATE TABLE deallocates all pages associated with the table. For this reason, TRUNCATE TABLE is faster and requires less transaction log space than DELETE. TRUNCATE TABLE is functionally equivalent to DELETE with no WHERE clause, but TRUNCATE TABLE cannot be used with tables referenced by foreign keys. Both DELETE and TRUNCATE TABLE make the space occupied by the deleted rows available for the storage of new data.

Permissions

DELETE permissions default to members of the sysadmin fixed server role, the db_owner and db_datawriter fixed database roles, and the table owner. Members of the sysadmin, db_owner, and the db_securityadmin roles, and the table owner can transfer permissions to other users.

SELECT permissions are also required if the statement contains a WHERE clause.

Examples

A. Use DELETE with no parameters

This example deletes all rows from the authors table.

USE pubs
DELETE authors

B. Use DELETE on a set of rows

Because au_lname may not be unique, this example deletes all rows in which au_lname is McBadden.

USE pubs
DELETE FROM authors
WHERE au_lname = 'McBadden'

C. Use DELETE on the current row of a cursor

This example shows a delete made against a cursor named complex_join_cursor. It affects only the single row currently fetched from the cursor.

USE pubs
DELETE FROM authors
WHERE CURRENT OF complex_join_cursor

D. Use DELETE based on a subquery or use the Transact-SQL extension

This example shows the Transact-SQL extension used to delete records from a base table that is based on a join or correlated subquery. The first DELETE shows the SQL-92-compatible subquery solution, and the second DELETE shows the Transact-SQL extension. Both queries remove rows from the titleauthors table based on the titles stored in the titles table.

/* SQL-92-Standard subquery */
USE pubs
DELETE FROM titleauthor
WHERE title_id IN 
   (SELECT title_id 
   FROM titles
   WHERE title LIKE '%computers%')

/* Transact-SQL extension */
USE pubs
DELETE titleauthor
FROM titleauthor INNER JOIN titles 
   ON titleauthor.title_id = titles.title_id
WHERE titles.title LIKE '%computers%'

E. Use DELETE and a SELECT with the TOP Clause

Because a SELECT statement can be specified in a DELETE statement, the TOP clause can also be used within the SELECT statement. For example, this example deletes the top 10 authors from the authors table.

DELETE authors 
FROM (SELECT TOP 10 * FROM authors) AS t1
WHERE authors.au_id = t1.au_id

See Also

CREATE TABLE

CREATE TRIGGER

Cursors

DROP TABLE

INSERT

SELECT

TRUNCATE TABLE

UPDATE