Delete
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.
Save $$$ on Your Home Phone Service with Phone.com
Examples:
A. DELETE with No Parameters
This example deletes all rows from the customer table.
DELETE customer
B. DELETE a Set of Rows
Because cars may not be unique, this example deletes all rows
where cars is Ford.
DELETE FROM automobiles
WHERE cars = 'Ford'
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.
Need cash to pay your bills? PayDay One can help you fast. Apply now!
/* 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%'
|
|
