Print this chapterPrint this chapter

Data Manipulation Language

DELETE statement

The DELETE statement removes rows from a record set. DELETE names the table or view that holds the rows that will be deleted and only one table or row may be listed at a time. WHERE is  a standard WHERE clause that limits the deletion to select records.

The DELETE syntax looks like this.

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

The rules for the DELETE statement are:

  1. If you omit a WHERE clause, all rows in the table are removed (except for indexes, the table, constraints).
  2. DELETE cannot be used with a view that has a FROM clause naming more than one table. (Delete can affect only one base table at a time.)

What follows are three different DELETE statements that can be used.

1. Deleting all rows from a table.

DELETE
FROM Discounts

2. Deleting selected rows:

DELETE
FROM Sales
WHERE stor_id = '6380'

3. Deleting rows based on a value in a subquery:

DELETE FROM Sales
WHERE title_id IN
(SELECT title_id FROM Books WHERE type = 'mod_cook')