我如何使用"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屋！