The DELETE statement facilitates the removal of data from the database. Like the SELECT statement, the DELETE statement contains a WHERE clause that specifies the conditions used to identify rows to be deleted. If you neglect to add a WHERE clause to your DELETE statement, all rows will be deleted from the target table. The following statement will delete all employees with the last name of Hooper from the employee table:

  DELETE FROM employee
  WHERE lname = 'HOOPER';

In some cases, the values needed for one or more of the conditions in your WHERE clause exist in another table. For example, your company may decide to outsource its accounting functions, thereby necessitating the removal of all accounting personnel from the employee table:

  DELETE FROM employee
  WHERE dept_id =
   (SELECT dept_id
   FROM department
   WHERE name = 'ACCOUNTING');

The use of the SELECT statement in this example is known as a          subquery and will be studied in detail in Chapter 5.

In certain cases, you may want to restrict the number of rows that are to be deleted from a table. For example, you may want to remove all data from a table, but you want to limit your transactions to no more than 100,000 rows. If the cust_order table contained 527,365 records, you would need to find a way to restrict your DELETE statement to 100,000 rows and then run the statement six times until all the data has been purged. The following example demonstrates how the ROWNUM pseudocolumn may be used in a DELETE statement to achieve the desired effect:

  DELETE FROM cust_order
  WHERE ROWNUM < 100000;
  COMMIT;

The UPDATE Statement

Modifications to existing data are handled by the UPDATE statement. Like the DELETE statement, the UPDATE statement includes a WHERE clause to specify which rows should be targeted. The following example shows how you might give a 10% raise to everyone making less than $40,000:

  UPDATE employee
  SET salary = salary * 1.1
  WHERE salary < 40000;

If you want to modify more than one column in the table, you have two choices: provide a set of column/value pairs separated by commas, or provide a set of columns and a subquery. The following two UPDATE statements modify the inactive_dt and inactive_ind columns in the customer table for any customer who hasn’t placed an order in the past year:

  UPDATE customer
  SET inactive_dt = SYSDATE, inactive_ind = 'Y'
  WHERE last_order_dt < SYSDATE — 365;
  UPDATE customer
  SET (inactive_dt, inactive_ind) =
   (SELECT SYSDATE, 'Y' FROM dual)
  WHERE last_order_dt < SYSDATE — 365;

The subquery in the second example is a bit forced, since it uses a query against the dual table to build a result set containing two literals, but it should give you an idea of how you would use a subquery in an UPDATE statement. In later chapters, you will see far more interesting uses for subqueries.

This article is excerpted from Mastering Oracle SQL by Sanjay Mishra and Alan Beaulieu (O'Reilly, 2004; ISBN  1590593006). Check it out at your favorite bookstore today. Buy this book now.