Take the 2-minute tour ×

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?

share|improve this question
Does the original query still return ~60 rows? –  OMG Ponies Apr 19 '10 at 19:20
Yes. The database was not modified in any way. –  doublep Apr 19 '10 at 19:23
Just a guess - can you add explicit aliases to all tables/views in the subquery as well as the update table and see if that changes your update result? –  dpbradley Apr 19 '10 at 19:35
No, sorry, I can't. This is a production database and I cannot just modify its structure like that. –  doublep Apr 19 '10 at 19:39
I think dpbradley is talking about modifying your SQL statement, not the actual view. For each table/view reference in your SQL, provide an alias for it and qualify each column name in your SQL accordingly. –  DCookie Apr 19 '10 at 20:34

6 Answers 6

up vote 4 down vote accepted

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 :-/

share|improve this answer

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.

share|improve this answer
No, some_table is a real table. Subquery, however, does contain one view. Can that be related? –  doublep Apr 19 '10 at 19:29
No, it shouldn't be related. "some_value" isn't related to the subquery, is it? Also, if the subquery results contain nulls, results can get flakey. –  Philip Kelley Apr 19 '10 at 20:39
some_value is in fact a string literal, so no it's not related. No nnulls either... –  doublep Apr 20 '10 at 17:57
In fact the view was responsible, although very indirectly, by eventually calling a PL/SQL function. I posted a self-answer with explanation. –  doublep Apr 21 '10 at 18:13

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?

share|improve this answer
I'll need to check that later as I currently (until tomorrow) don't have access. Hardly, but then I don't see other possible reasons. –  doublep Apr 19 '10 at 20:26

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 ?

share|improve this answer
I'm certain there is no row-level security because I work with that database (normally programmatically, but this time I had to issue queries by hand). What do you mean by database links? –  doublep Apr 20 '10 at 17:56

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

share|improve this answer
No, it's a direct SQL command. –  doublep Apr 20 '10 at 17:54

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.

share|improve this answer
No. I debugged it and posted a self-answer, but cannot accept it just yet. –  doublep Apr 21 '10 at 18:39

Your Answer


