[SOLVED] Oracle WITH DELETE by row number

Oracle WITH DELETE by row number

I am writing a procedure which removes old rows by their viewed date:

;WITH pv AS (
    SELECT
        RN = ROW_NUMBER() OVER (ORDER BY viewed DESC)
    FROM
        previouslyViewed
    WHERE
        userId = @userId
)
DELETE FROM pv WHERE RN >= 10

This works in SQL Server, but not in Oracle.

Oracle doesn't support the WITH DELETE combination. Nor does it support the DELETE ORDER BY combination (which could theoretically be used with rownum to achieve the same result). I have tried to create a temporary view with the rownum and delete from that, but I get an Oracle error - seemingly you cannot delete from a view when rownum is used.

Does anyone have any pointers?

Solution

You could do this instead:

DELETE FROM previouslyViewed WHERE pkcol IN
( SELECT pkcol FROM
  (
      SELECT pkcol, ROW_NUMBER() OVER (ORDER BY viewed DESC) RN
      FROM previouslyViewed
      WHERE userId = :userId
  )
  WHERE RN >= 10
);

(changing pkcol to the primary key column(s) of the table)