How to work around ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.

Question

I want to lock one record in a table. The record is specified as "the next that has ID greater than..."

CREATE TABLE test (id number);

SELECT id
FROM (SELECT id 
      FROM test
      WHERE id > 10
      ORDER BY id)
WHERE ROWNUM = 1
FOR UPDATE;

This seems intuitive and easy. But it is not. Any ideas?

P.S.

I do need the existing query to remain the same because it is a cursor and there are several places that use this cursor's %rowtype.

Answer 1

I think you're going to need something like:

SELECT id
  FROM test
 WHERE id =
       (SELECT MIN(id) 
          FROM test
         WHERE id > 10)
FOR UPDATE;