CODEWITHSUNDEEP
sqloracle-database
Liam
Liam

Reputation: 8092

SQL to delete the oldest records in a table

I'm looking for a single SQL query to run on an oracle table that will retain n number of records in a table and delete the rest

I tried the following 

delete from myTable where pk not in 
(SELECT pk FROM myTable where rownum <5 order by created DESC)

But it appears that I cannot have order by in the nested select.

Any help appreciated

Upvotes: 5

Views: 9370

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132580

When you use ORDER BY with ROWNUM the ROWNUM is applied first, so you don't get the results you expect. You could modify your SQL to:

delete from myTable where pk not in 
( SELECT pk FROM 
   ( SELECT pk FROM myTable  order by created DESC)
  where rownum <5
)

There are many other ways to write this. If the table is large and most rows will be deleted then maybe this will be faster:

delete from myTable where created < 
( SELECT MIN(created) FROM 
   ( SELECT created FROM myTable order by created DESC)
  where rownum <5
)

Upvotes: 15

Related Questions