Home arrow Oracle arrow Page 5 - Introduction to SQL

The DELETE Statement - Oracle

Interested in learning more about the SQL language? Read on to learn some of the useful features of this language. This article is excerpted from Mastering Oracle SQL by Sanjay Mishra and Alan Beaulieu (O'Reilly, 2004; ISBN 1590593006).

TABLE OF CONTENTS:
  1. Introduction to SQL
  2. A Brief History of SQL
  3. The SELECT Statement
  4. Ordering your results
  5. The DELETE Statement
  6. The MERGE Statement
By: O'Reilly Media
Rating: starstarstarstarstar / 14
March 16, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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 hasnt 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.


dual is an Oracle-provided table containing exactly one row with one column. It comes in handy when you need to construct a query that returns exactly one row.

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.



 
 
>>> More Oracle Articles          >>> More By O'Reilly Media
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: