Updating strings using the in operator in the where section

Question

I came across SQL behavior that I don't understand. I needed to update several rows in a table at once; started with their search:

SELECT * FROM some_table WHERE field1 IN (SELECT ...) 

This returned a selection of about 60 rows. Now I was pretty sure that I got the subquery on the right, so I only changed the first part:

 UPDATE some_table SET field2 = some_value WHERE field1 IN (SELECT ...) 

In other words, it was exactly the same as the first query after WHERE . However, this led to an update of 0 rows, whereas I would expect these 60. Please note that the above statement would change the value of field2 , that is, I checked that some_value not present in the selected rows.

The subquery was a modestly complex SQL part with two (different) tables, 1 view, a join, and its own WHERE . In case it matters, it happened with Oracle Database 10g.

So the question is, why didn't UPDATE touch the rows returned by SELECT ?

Answer 1

Finally nailed it. It turned out that the view used in the subquery is indirectly (through another view) called a stored procedure / function. Then the procedure got access to the table, which was changed in UPDATE . As a result, Oracle some_table exception for setting "table some_table , and the function may not see the result" (do not remember the exact text). But in the end, the function used when other then return null , so the error was effectively hidden, and the subquery returned nothing at all - and, in turn, UPDATE had no effect.

Moral: Never use excessive exceptions for traps. I follow this rule in other languages, but apparently not in PL / SQL: - /

Answer 2

If "some-table" is actually a view, you may have encountered a problem where the system cannot decide how to update the tables underlying the view.

Answer 3

I had a problem when I made a mistake in the column name, but in another, select the column with the same name, so my internal query "worked" by joining the external table.
If you just run an internal query on its own (without an external selection or update), does it work?

Answer 4

There might be a Row-Level Security (also known as a Virtual Private Database), where you were given permission to read the table rows, but not to update them.

Any links to the database?

Answer 5

Is this that your field1 is not the first column returned from your subquery? I suspect your IN will only compare the value with the first column of results.