Menu
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
472
Use the DELETE statement with a WHERE clause to specify a search condition. The DELETE statement removes zero or more rows of a table, depending on how many rows satisfy the search condition that you specify in the WHERE clause.
Delete a recordOpen the table in Datasheet View or form in Form View. Select the record or records that you want to delete. To select a record, click the record selector next to the record, if the record selector is available.
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
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
