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

lockingora-02014oraclesql-order-by

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.

Best Answer

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;
Related Topic