使用“in”更新行“where”中的运算符条款
我偶然发现了我不理解的 SQL 行为。我需要一次更新表中的几行；首先找到它们：
SELECT * FROM some_table WHERE field1 IN (SELECT ...)
这返回了大约 60 行的选择。现在我非常有信心我的子查询是正确的，所以我只修改了第一部分：
UPDATE some_table SET field2 = some_value WHERE field1 IN (SELECT ...)
换句话说，这与 WHERE 之后的第一个查询完全相同。但是，它导致更新了 0 行，而我希望更新 60 行。请注意，上面的语句会更改 field2，即我验证了 some_value 不存在于所选行中。
子查询是一个适度复杂的 SQL 片段，包含 2 个（不同的）表、1 个视图、连接和它自己的 WHERE 子句。如果这很重要，Oracle 数据库 10g 就发生过这种情况。
所以，问题是，为什么UPDATE没有触及SELECT返回的行？
I stumbled upon SQL behavior I don't understand. I needed to update several rows in a table at once; started with just finding them:
SELECT * FROM some_table WHERE field1 IN (SELECT ...)
This returned a selection of about 60 rows. Now I was pretty confident I got the subquery right, so I modified the first part only:
UPDATE some_table SET field2 = some_value WHERE field1 IN (SELECT ...)
In other words, this was exactly as the first query after the WHERE. However, it resulted in 0 rows updated, whereas I would expect those 60. Note that the statement above would change field2, i.e. I verified that some_value was not present in the selected rows.
The subquery was a modestly complicated SQL piece with 2 (different) tables, 1 view, joins and its own WHERE clause. In case this matters, it happened with Oracle Database 10g.
So, the question is, why UPDATE didn't touch the rows returned by SELECT?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
终于把它敲定了。事实证明，子查询选择中使用的视图间接（通过另一个视图）称为存储过程/函数。然后，该过程访问在 UPDATE 中修改的表。结果，Oracle 抛出异常“表
some_table正在被修改，函数可能看不到结果”（不记得确切的文本）。但是该函数最终使用了when other then return null，因此错误被有效地隐藏了，并且子查询根本没有返回任何内容 - 而UPDATE也没有返回任何内容。影响。道德：永远不要使用过于宽泛的异常捕获器。我在其他语言中遵循此规则，但显然在 PL/SQL 中没有遵循此规则：-/
Finally nailed it down. Turned out that the view used in subquery selection indirectly (through another view) called a stored procedure/function. The procedure then accessed the table that was being modified in
UPDATE. As a result, Oracle threw exception to the tune of "tablesome_tableis being modified and function may not see the result" (don't remember the exact text). But the function usedwhen other then return nullin the end, so the error was effectively hidden and the subquery didn't return anything at all — and in turnUPDATEhad no effect.Moral: never use overbroad exception catchers. I follow this rule in other languages, but apparently not in PL/SQL :-/
如果“some-table”实际上是一个视图，您可能遇到了系统无法确定如何更新视图下的表的问题。
If "some-table" is actually a view, you may have hit an issue where the system is not able to work out how to update the tables that underlie the view.
我曾经遇到过一个问题，我输错了列名，但在另一个选择中有一个同名的列，所以我的内部查询通过连接外部表来“工作”。
如果您只是单独运行内部查询（没有外部选择或更新），它可以工作吗？
I had a problem once where I had mistyped a column name, but there was a column with the same name in the other select, so my inner query "worked" by joining against the outer table.
If you just run the inner query by itself (no outer select or update) does it work?
可能是行级安全性（也称为虚拟专用数据库），您被授予读取表行的权限，但无权更新它们。
涉及任何数据库链接吗？
Could be Row-Level Security (also known as Virtual private Database) where you've been given permission to read rows of a table, but not to update them.
Any database links involved ?
您的 field1 是否不是子查询返回的第一列？我怀疑您的 IN 只会将该值与结果的第一列进行比较。
Is it that your field1 is not the first column returned from your subquery? I suspect that your IN would only compare the value to the results' first column.