How to delete all the rows in a table in an Oracle database leaving just the first 2 rows.

by chirag
(india)

There is a table with 100 entries. I want to delete all the rows except first two row. How should I do it?


This is a very good question and one that often comes up, however it is really a trick question. What do we mean by that? Well when you query a table in your Oracle database unless you specify how the results should be ordered there is no guarantee that the order will be the same from one query to the next. So if the order changes every time, how can you determine which are the first 2 rows in the table?

If we know which are the first 2 rows in the table (for example the 2 rows in the employee table with the earliest hire dates), then it's just a case of constructing a SQL statement to perform the delete operation. The SQL DELETE statement itself is rather simple, the only difficulty lies in constructing the WHERE clause.

Let's have a look, continuing with the example of using the employees table and wanting to delete all but the 2 longest-serving employees (those 2 with the earliest start dates).

At first it seems simple we just find the 2nd earliest start date and remove all the rows with a start date after that. The SQL statement for this would be:

delete from employee where hire_date >
(select max(hire_date) from
(select hire_date from employee order by hire_date asc)
where rownum <3)

However there is a problem with this SQL statement - it doesn't do quite what we want. The innermost sub query
(select hire_date from employee order by hire_date asc)
returns the hire dates in order of earliest to latest and the query using that sub query returns the later of the 2 earliest hire dates (by virtue of the where clause just selecting the first 2 rows from the sub query), but unless each employee has a unique hire date we can't guarantee that we will delete all but the 2 earliest hired employee records.

What we need therefore is a unique identifier for each record. This gives us a choice - for this particular table we could use the employee_id or for a more generic solution we can use the rowid which Oracle automatically assigns to every row of a table as the row is added to the table.

Let's look at the SQL for the generic solution first:

SQL> delete from employee where rowid not in
     (select rowid from
       (select hire_date from employee
       order by hire_date asc)
     where rownum <3)


This time we retrieve the rowids for the 2 records with the earliest hire dates by combining the two sub-queries and then just delete all the records in the employee table with different rowids.

The SQL statement using the employee_id as the unique identifier is very similar (unsurprisingly) to the generic solution:

SQL> delete from employee
     where employee_id not in
     (
       select employee_id from
         (select employee_id from new_emp
         order by hire_date asc)
     where rownum <3
     )


Again the 2 sub-queries combine to return just the 2 records with the earliest hire_dates. We have to use 2 sub-queries because otherwise the rownum pseudo-column would be generated before the results were sorted which would mean that the first 2 records returned by the query (which could be any 2 rows in the table) would be returned and then sorted in date order. That obviously would not give us what we want as Oracle does not guarantee that the order of the rows won't change from one query to the next.

See also our series of Oracle SQL tutorials and the Oracle training page for information about formal Oracle training either on-line or in person.

Comments for How to delete all the rows in a table in an Oracle database leaving just the first 2 rows.

Average Rating starstarstarstarstar

Click here to add your own comments

Jul 01, 2013
Rating
starstarstarstarstar
According to me
by: Bighnaraj Dalai


delete where rowid not in(select rowid from where rownum <3)

May 29, 2012
Rating
starstarstarstarstar
deleting all rows by leaving first 2 rows
by: venkat

Excellent answers
Thanking You.

May 29, 2012
Rating
starstarstarstarstar
deleting first 2 rows
by: venkat

excellent answers.
Thanking You.

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.