Re: Update not behaving as hoped

The sql you wrote first is valid. It is perfectly legal to reference a column from an outside table (table1) in a sub-select (table2). Hence no syntax error.

The "code" column will simply be replaced by whatever value is currently in the outer select (table1), as if it were a constant.

All you are doing is confirming that "code" in the left side of the predicate is equal to "code" in the right side, selected as many times as there are rows in table2. Hence the update to all rows.

When you pull the statement out of the sub-select, you get an error: you are not implicitly referencing an outer query column anymore, you're simply using an invalid column name - the context of execution of a sub-select is not the same as as single select, otherwise correlated sub-queries would never be possible.

If you coded with an explicit table alias, this would come back as an error in the original:

update table1
set table1.org=0
where table1.code in (select table2.code from table2)

The fact it doesn't means Oracle - and MSSQL - are taking a default that is not what you expected. But it's not illegal SQL.

Cheers
Nuno Souto
in sunny Sydney, Australia

On Wed Nov 3 11:06 , Jack van Zanen sent:

>This query is not a correlated subquery, nor is it intended to be.
> 
>I just wish to update table1 for which the code exists in the per_code field in
table2. As per below query which does exactly that.
> 
>update
>table1
>set Org=0
>where code in (select per_code from table2);

> >updatetable1
> >set Org=0
> >
> >where code in (select code from table2); 

--
http://www.freelists.org/webpage/oracle-l