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

Question

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. This is a regular table:

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

The primary key is the field identifier.

Is it possible to use rownum with select for update ?

Is there another solution that uses select for update , but also only a group of rows is selected, and not all the results from the selection?

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

Thanks.

Answer 1

It works?:

 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; 

Answer 2

What is event_table? It seems that the "event_table" is actually a view, the results of which are a collection (different, group, etc.). You cannot prepare aggregated results for updates. I am not 100% sure, but rownum can have the same effect as it is indefinite.