我如何使用"FOR UPDATE"?在Oracle上加入JOIN? [英] How can I use "FOR UPDATE" with a JOIN on Oracle?
问题描述
答案另一个SO问题是使用此SQL查询:
The answer to another SO question was to use this SQL query:
SELECT o.Id, o.attrib1, o.attrib2
FROM table1 o
JOIN (SELECT DISTINCT Id
FROM table1, table2, table3
WHERE ...) T1 ON o.id = T1.Id
现在,我想知道如何与关键字FOR UPDATE
一起使用此语句.如果我只是将其附加到查询中,Oracle会告诉我:
Now I wonder how I can use this statement together with the keyword FOR UPDATE
. If I simply append it to the query, Oracle will tell me:
ORA-02014:无法从视图中选择FOR UPDATE
ORA-02014: cannot select FOR UPDATE from view
我是否必须修改查询,或者使用Oracle可以做到这一点? 使用MySql,该语句可以正常工作.
Do I have to modify the query or is there a trick to do this with Oracle? With MySql the statement works fine.
推荐答案
尝试:
select .....
from <choose your table>
where id in (<your join query here>) for UPDATE;
编辑:考虑到您链接到的问题(该问题询问了如何免除IN
),这似乎有点违反直觉,但如果您的加入返回,仍可能会有所帮助限制集.但是,没有解决方法:oracle异常是不言自明的; oracle由于DISTINCT
的原因不知道要锁定哪些行.您可以不使用DISTINCT
或在视图中定义所有内容,然后在没有显式锁定的情况下对其进行更新:
EDIT: that might seem a bit counter-intuitive bearing in mind the question you linked to (which asked how to dispense with an IN
), but may still provide benefit if your join returns a restricted set. However, there is no workaround: the oracle exception is pretty self-explanatory; oracle doesn't know which rows to lock becasue of the DISTINCT
. You could either leave out the DISTINCT
or define everything in a view and then update that, if you wanted to, without the explicit lock: http://www.dba-oracle.com/t_ora_02014_cannot_select_for_update.htm
这篇关于我如何使用"FOR UPDATE"?在Oracle上加入JOIN?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!