oracle 如何解决 ORA-02014：无法从带有 DISTINCT、GROUP BY 的视图中选择 FOR UPDATE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5873051/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to solve ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY
提问by AmirT
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:
event_table 不是视图。这是一张普通表：
create table event_table
(
id number,
creation_date date,
status number,
info clob
);
The primary key is the field id.
主键是字段 ID。
Can I use rownumwith select for updateat all?
我可以用rownum用select for update呢？
Is there another solution where using select for updatebut also selecting just a group of rows and not all the results from the select?
是否有另一种解决方案使用select for update但也只选择一组行而不是选择的所有结果？
For example, I have a task that runs every X internal and needs to use select for updatefor 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 rownumfor that.
例如，我有一个任务运行每个 X 内部并需要select for update用于该表，但是如果选择返回 500 行，我只想每次处理其中的 100（一种分页）。这就是我尝试这样做的原因rownum。
Thanks.
谢谢。
回答by Jens Schauder
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;
