views:

55

answers:

1

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.

+4  A: 

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;
DCookie
There are syntax problems but I get the idea. Thanks.
jva
Changed the query a bit...
DCookie