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

ora-02014oracle

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.

Best Answer

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