3 4
The DELETE statement is used to remove (delete) a row or rows from a table or view. DELETE does not affect the table definition; it simply deletes rows of data from the table. The basic syntax for the DELETE statement is shown here:
DELETE [FROM] table_name | view_name [FROM table_sources ] WHERE search_condition
The first FROM keyword is optional, as is the second FROM clause. Rows are not deleted from the table sources in the second FROM clause; they are deleted from only the table or view specified after DELETE.
By using the WHERE clause with DELETE, you can specify certain rows to delete from a table. For example, to delete all rows from the items table that have an item_category value of toys, run the following statement:
DELETE FROM items WHERE item_category = 'toys' GO
This statement deletes one row from our items table.
You can use a second FROM clause with one or more table sources to specify other tables and views that can be used in the WHERE search condition. For example, to delete rows from the items table that correspond to the rows in the two_newest_items table, run the following statement:
DELETE items FROM two_newest_items WHERE items.item_id = two_newest_items.item_id GO
Notice that in this statement we left out the first optional FROM keyword. The two rows in the two_newest_items table have item_id values of 2 and 3. The items table contains item_id values of 1 and 2, so the row with 2 as its item_id value (the one that matched the search condition) gets deleted. The two rows in the two_newest_items table (the table source) are not affected.
To delete all rows from a table, use DELETE without a WHERE clause. The following DELETE statement will delete all rows in the two_newest_items table.
DELETE FROM two_newest_items GO
The two_newest_items table is now an empty table—it contains no data. If you want to delete the table definition as well, use the DROP TABLE command, as follows. (This command is explained in Chapter 15.)
DROP TABLE two_newest_items GO
MORE INFO
To learn more ways of using the DELETE statement, such as using joined tables as table sources and using table and query hints, check the Books Online index for "DELETE" and select the subtopic "Described."