CODEWITHSUNDEEP
oracle-database
user3745193
user3745193

Reputation: 97

How to delete the recent (in date) records from a table in oracle?

i want to delete the last recent record from my table.

Code to select the last record:

SELECT * FROM 
( select ID, NAME, 
createdon
 from Person where age= 30 
order by createdon DESC) 
WHERE ROWNUM <= 1;

i tried this to delete the last records:

DELETE * FROM 
( select ID, NAME, 
createdon
 from Person where age= 30 
order by createdon DESC) 
WHERE ROWNUM <= 1;

It 's not working. ERROR: "invalid table name"

Any help please, Thank you

Upvotes: 0

Views: 3128

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

As OracleUser said, you can't delete from an in-line view. You can use your original inner query to delete based on all three columns it selects, assuming they will uniquely identify a single row:

delete from person
where (id, name, createdon) = (
  select * from (
    select id, name, createdon
    from person
    where age = 30 
    order by createdon desc
  ) 
  where rownum <= 1
);

SQL Fiddle.

If ID is a primary or unique key you can do this instead:

delete from person
where id =
(
  select id
  from (
    select id, row_number() over (order by createdon desc) as rn
    from person where age = 30
  )
  where rn = 1
);

If it is not then you can use rowid instead, replacing both references to id in the statement.

The innermost query is basically the same as the inner query in your select, but I'm using the row_number analytic function to assign the equivalent of rownum. The next layer out filters out everything except the most recent, based on the row_number calculation.

You can use rank() instead but could get two record with exactly the same times, and would need to decide whether both should be deleted, or if you should have some additional way to break the tie - e.g. order by createdon desc, id desc. As I've shown it here, row_number() also breaks the tie arbitrarily but can have the explicitly extra ordering to control that, as would rownum. And there's also dense_rank().

SQL Fiddle.

This is a bit simpler but would also delete multiple rows, if you had more than one with the same createdon value:

delete from person
where createdon =
(
  select max(createdon)
  from person
  where age = 30
);

Upvotes: 1

Related Questions