Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.4k views
in Technique[技术] by (71.8m points)

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

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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;

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...