Collection Contents Index Changing data using UPDATE CHAPTER 9.  Using SQL in Applications pdf/chap8.pdf

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.


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:


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 
   SUM(product.unit_price * sales_order_items.quantity) as "Value Sold"
FROM  product JOIN sales_order_items
ON = sales_order_items.prod_id

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

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

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


Top of page  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.


The syntax of truncate table is:


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.

Top of page  

Collection Contents Index Changing data using UPDATE CHAPTER 9.  Using SQL in Applications pdf/chap8.pdf