Updating rows using "in" operator in "where" clause
- by doublep
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?
