ASA SQL User's Guide
  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 

Use the WHERE clause to specify which rows to remove. If no WHERE clause appears, the DELETE statement remove all rows in the table.

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 you to delete rows 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