DELETE Statement

Removes rows from a table.

Syntax

DELETE [FROM] {table_name | view_name}
    [WHERE clause]

where

table_name | view_name =

[[database_name.]owner.]{table_name | view_name}

Specifies the table or view used in the DELETE statement. If the table or view exists in another database, use a fully qualified table_name or view_name (database_name.owner.object_name).

Because DELETE can affect only one base table at a time, you cannot use DELETE with a view that has a FROM clause naming more than one table.

WHERE clause =

WHERE {search_conditions | CURRENT OF cursor_name}

Is used to perform a searched delete (using search_conditions) or a positioned delete (using CURRENT OF cursor_name). When no WHERE clause is given in the DELETE statement, all rows in the table are removed. The table itself, along with its indexes, constraints, and so on, remains in the database.

search_conditions

Specifies the criteria for a searched delete. For details, see the Search Conditions topic.

The IDENTITYCOL keyword can be used in the place of a column_name that has the IDENTITY property. For more information, see the CREATE TABLE statement.

CURRENT OF cursor_name

Specifies the criteria for a positioned delete against the current row within the specified cursor. Cursor operations affect only the single row on which the cursor is positioned. For details, see the Cursors topic.

Remarks

The TRUNCATE TABLE statement and the DELETE statement without a WHERE clause are functionally equivalent, but TRUNCATE TABLE is faster. The DELETE statement removes rows one at a time and logs each row deletion; the TRUNCATE TABLE statement deletes all rows by logging only the page deallocations. Both DELETE and TRUNCATE TABLE reclaim the space occupied by the data and its associated indexes.

Transact-SQL Extension for the DELETE Statement

In addition to the syntax shown earlier, Transact-SQL includes a feature that allows you to select data from a table or tables and delete corresponding data from the first-named table. The functionality provided is similar to that of using a correlated subquery as a search_condition in the WHERE clause as a standard update.

Transact-SQL extension syntax:

DELETE [FROM] {table_name | view_name}
    [FROM {table_name | view_name}
        [, {table_name | view_name}]...]
            [..., {table_name16 | view_name16}]]
[WHERE clause]

where

FROM

Lets you name more than one table or view to use with a WHERE clause to specify which rows to delete. The additional FROM clause allows you to delete rows from one table based on data stored in other tables, giving you similar functionality to a correlated subquery (SELECT statement in the WHERE clause).

For an example of the differences between correlated subqueries and this Transact-SQL extension, see the examples later in this section.

Permission

DELETE permission defaults to the table owner, who can transfer it to other users.

Examples

A.    DELETE with No Parameters

This example deletes all rows from the authors table.

DELETE authors

B.    DELETE a Set of Rows

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

DELETE FROM authors
    WHERE au_lname = 'McBadden'

C.    DELETE 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 that is currently fetched from the cursor.

DELETE FROM authors
    WHERE CURRENT OF complex_join_cursor

D.    DELETE Based on a Subquery or Using the Transact-SQL Extension

This example shows the Transact-SQL extension used to delete records from a base table based on a join or correlated subquery. The first DELETE shows the ANSI-compatible subquery solution, and the second DELETE shows the Transact-SQL extension.

/* ANSI-Standard subquery */
DELETE FROM titleauthor 
    WHERE au_id IN 
        (SELECT a.au_id 
            FROM authors a, titles t, titleauthor ta
                WHERE a.au_id = ta.au_id
                AND ta.title_id = t.title_id
                AND t.title LIKE '%computers%')

/* Transact-SQL extension */
DELETE FROM titleauthor 
    FROM authors a, titles t
        WHERE a.au_id = titleauthor.au_id
        AND titleauthor.title_id = t.title_id
        AND t.title LIKE '%computers%'

See Also