11

I want to lock a group of records using the following query:

select *
  from (select *
          from event_table
         where status = 'S'
        order by creation_data asc
       )
 where rownum <=10
for update;

event_table is not a view. It is a regular table: 

create table event_table
(
 id            number, 
 creation_date date, 
 status        number, 
 info          clob
);

The primary key is the field id.

Can I use rownum with select for update at all?

Is there another solution where using select for update but also selecting just a group of rows and not all the results from the select?

For example, I have a task that runs every X internal and needs to use select for update for that table, but if the select returns 500 rows, I just want to handle 100 of them each time (kind of paging). That is why I tried rownum for that.

Thanks.

Doug Porter
  • 7,721
  • 4
  • 40
  • 55
AmirT
  • 161
  • 1
  • 2
  • 6

1 Answers1

19

Does this work?:

select * from event_table where id in 
(
    SELECT id
    FROM (SELECT *
        FROM event_table
        WHERE status = 'S'
        ORDER BY CREATION_DATA ASC)
        WHERE ROWNUM <=10
)
FOR UPDATE;
Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
  • well, the query succeeded, i'll check it now if the rows are locked as needed. thanks a lot. – AmirT May 04 '11 at 08:25