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

Posted by doublep on Stack Overflow See other posts from Stack Overflow or by doublep
Published on 2010-04-19T19:13:11Z Indexed on 2010/04/19 23:53 UTC
Read the original article Hit count: 221

Filed under:
|
|

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?

© Stack Overflow or respective owner

Related posts about sql

Related posts about subquery