Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-00928: missing SELECT keyword using WITH clause

user12251389Jun 14 2016 — edited Jun 14 2016

I want to keep the last 20 records in VMR table and delete all other records. The VMR table has 5000000 records and its growing. I also have create_date column which has date datatype in VMR table and it has non unique index. I tried using rownum and WITH clause to delete the records and keep the last 20 records using below query but its givign an error as

ORA-00928: missing SELECT keyword

Below is the query i used :

WITH CTE AS (

SELECT t.*,

       ROW_NUMBER() OVER(ORDER BY create_date DESC) as rnk

FROM VMR t)

DELETE FROM CTE

WHERE CTE.rnk > 20;

This post has been answered by Sergei Krasnoslobodtsev on Jun 14 2016
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 12 2016
Added on Jun 14 2016
3 comments
10,047 views