Removes rows from a table.
DELETE [FROM] {table_name | view_name}
[WHERE clause]
where
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.
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.
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.
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.
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
For an example of the differences between correlated subqueries and this Transact-SQL extension, see the examples later in this section.
DELETE permission defaults to the table owner, who can transfer it to other users.
This example deletes all rows from the authors table.
DELETE authors
Because au_lname may not be unique, this example deletes all rows where au_lname is McBadden.
DELETE FROM authors WHERE au_lname = 'McBadden'
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
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%'
CREATE TABLE | INSERT |
CREATE TRIGGER | SELECT |
Cursors | TRUNCATE TABLE |
DROP TABLE | UPDATE |
DROP TRIGGER |