错误的思路sql语句:
select rownum,a.id,a.deptno FROM (select emp.* from emp where state=1 order by deptno asc) a
where rownum <=10 for update;
错误原因:
SQL 错误: ORA-02014: 不能从具有 DISTINCT, GROUP BY 等的视图选择 FOR UPDATE
02014. 00000 - "cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc."
oracle:对查询满足条件的数据上锁,正确的sql如下:
select id,deptno FROM emp
where id in
(select id from
(select id from emp
where state=1 order by deptno asc)
where rownum <=10
)
order by deptno
for update;
内部排序后,取到相应的数据,但是用了in后,数据乱序,需重新order。
总结:
DISTINCT, GROUP BY,order by等不要直接在from后的子查询中,否者无法使用for update。