User's Guide
   PART 1. Working with Databases
     CHAPTER 8. Adding, Changing, and Deleting Data       

Deleting data using DELETE

Simple DELETE statements have the following form:

DELETE [ FROM ] table-name

WHERE column-name = expression

You can also use a more complex form, as follows

DELETE [ FROM ] table-name

FROM table-list

WHERE search-condition

The WHERE clause 

The WHERE clause specifies which rows are to be removed. If no WHERE clause is given in the DELETE statement, all rows in the table are removed.

The FROM clause 

The FROM clause in the second position of a DELETE statement is a special feature allowing you to select data from a table or tables and delete corresponding data from the first-named table. The rows you select in the FROM clause specify the conditions for the delete.

Example 

This example uses the sample database. To execute the statements in the example, you should set the option WAIT_FOR_COMMIT to OFF. The following statement does this for the current connection only:

SET TEMPORARY OPTION WAIT_FOR_COMMIT = 'OFF'

This allows rows to be deleted even if they contain primary keys referenced by a foreign key, but does not permit a COMMIT unless the corresponding foreign key is deleted also.

The following view displays products and the value of that product that has been sold:

CREATE VIEW ProductPopularity as 
SELECT  product.id, 
   SUM(product.unit_price * sales_order_items.quantity) as "Value Sold"
FROM  product JOIN sales_order_items
ON product.id = sales_order_items.prod_id
GROUP BY product.id

Using this view, you can delete those products which have sold less than $20,000 from the product table.

DELETE
FROM product
FROM product NATURAL JOIN ProductPopularity
WHERE "Value Sold" < 20000

You should roll back your changes when you have completed the example:

ROLLBACK

  Deleting all rows from a table

You can use the TRUNCATE TABLE statement as a fast method of deleting all the rows in a table. It is faster than a DELETE statement with no conditions, because the delete logs each change, while truncate table operations are not recorded individually in the transaction log.

The table definition for a table emptied with the TRUNCATE TABLE statement remains in the database, along with its indexes and other associated objects, unless you enter a DROP TABLE statement.

You cannot use TRUNCATE TABLE if another table has rows that reference it through a referential integrity constraint. Delete the rows from the foreign table, or truncate the foreign table and then truncate the primary table.

TRUNCATE TABLE syntax 

The syntax of truncate table is:

TRUNCATE TABLE table-name

For example, to remove all the data in the sales_order table, type the following:

TRUNCATE TABLE sales_order

A TRUNCATE TABLE statement does not fire triggers defined on the table.