Updating rows using "in" operator in "where" clause

Question

Hi.

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?

Answer 1

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.

Answer 2

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?

Answer 3

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 ?

Answer 4

Are you using SQL Developer? make sure you are committing your session....

Answer 5

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 "table some_table is being modified and function may not see the result" (don't remember the exact text). But the function used when other then return null in the end, so the error was effectively hidden and the subquery didn't return anything at all — and in turn UPDATE had no effect.

Moral: never use overbroad exception catchers. I follow this rule in other languages, but apparently not in PL/SQL :-/

Answer 6

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.