Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

   
  Oracle Tips by Burleson

DELETE

Like the UPDATE statement, the DELETE statement removes all rows identified by the WHERE clause.  This is another data manipulation command, which means that we can roll back the deleted data, and that to make our changes permanent, we need to issue a commit.  We have already looked at a couple of the DELETE formats.

SQL> delete from author;

10 rows deleted.

SQL> rollback;

Rollback complete.

SQL> delete author;

10 rows deleted.

SQL> rollback;

Rollback complete.

Both commands deleted all the rows in the table.  You cannot delete part of a row.  If you want to remove some of the data such as setting all columns to NULL except the author_key, you would use the UPDATE statement.  The basic format of the command is:

delete from <table name> where <expression>;

Every row that matches the expression will be removed from the table. 

SQL> delete from author
  2  where
  3    author_key in ('A101','A103','A120'); 

2 rows deleted.

SQL> select author_key from author;

AUTHOR_KEY
-----------
A102
A104
A105
A106
A107
A108
A109
A110

8 rows selected.

Notice that there is no author with an author_key = A120, so only two authors were deleted.

Let’s delete the order with the smallest quantity.

SQL> delete from sales
  2  where
  3    quantity = (select
  4                  min(quantity)
  5                from
  6                  sales); 

1 row deleted.

Any valid WHERE clause is acceptable to identify which rows to delete.  I can get the same results as above using a nested query and the order number.

SQL> delete from sales
  2  where
  3    order_number = (select
  4                      order_number
  5                    from sales
  6                    where quantity = (select
  7                                        min(quantity)
  8                                      from
  9                                        sales)) 

1 row deleted.

But, what happens when I run the above query again?

SQL> delete from sales
  2  where
  3    order_number = (select
  4                      order_number
  5                    from sales
  6                    where quantity = (select
  7                                        min(quantity)
  8                                      from
  9                                        sales));
  order_number = (select
                  *
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row

What happened?  Well, the first time I ran the query, it returned one row because there was only one order number that had the minimum quantity.  That order had been deleted.  Now, there are many orders that have the new minimum quantity and the query fails.  This is an example of writing a query expecting one row, testing it and it working, but then having it fail in other tests.  How do I fix it?  Change the equals to IN.

SQL> delete from sales
  2  where
  3    order_number in (select
  4                      order_number
  5                     from sales
  6                       where quantity = (select
  7                                           min(quantity)
  8                                         from
  9                                           sales));  

47 rows deleted. 

There were 47 order numbers with the new minimum quantity.

What happens if we rerun the pubs_db.sql script while logged on as the PUBS user?  The DROP USER command fails (you can’t drop yourself while logged on), the CREATE TABLE commands fail (the tables already exist), but the INSERT commands all succeed.  Now, we have duplicate rows.  We can use the author key to remove the duplicate rows in the author table.

SQL> select author_key from author;
AUTHOR_KEY
-----------
A101
A102
A103
A104
A105
A106
A107
A108
A109
A110
A101
A102
A103
A104
A105
A106
A107
A108
A109
A110

20 rows selected.

I want to keep one row for each author key.  My method is to select all the rows, group them by author_keys, select the largest rowid from each group (this is the last inserted row for each author key), and delete the rest.

SQL> delete from author
  2  where rowid not in (select
  3                        max(rowid)
  4                      from author
  5                        group by author_key);  

10 rows deleted. 

SQL> select author_key from author;
AUTHOR_KEY
-----------
A101
A102
A103
A104
A105
A106
A107
A108
A109
A110

10 rows selected.

This query leaves only the last inserted row for each author key. 

Throughout the discussion of INSERTs, UPDATEs and DELETEs, we have seen the ROLLBACK command used to undo the changes.  The ROLLBACK command is part of how any database management system implements transactions.


The above text is an excerpt from:

Easy Oracle SQL
Get Started Fast Writing SQL Reports with SQL*Plus

ISBN 0-9727513-7-8

by John Garmany
 


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 

 

email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.