The DELETE Statement

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.

Deleting Individual Rows

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.

Deleting All Rows

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."



Microsoft SQL Server 2000 Administrator's Companion
Microsoft SQL Server 2000 Administrators Companion
ISBN: B001HC0RPI
EAN: N/A
Year: 2005
Pages: 264

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net