Re: Update not behaving as hoped
- From: "dbvision@xxxxxxxxxxxx" <dbvision@xxxxxxxxxxxx>
- To: Mindaugas Navickas <mnavickas@xxxxxxxxx>, Jack van Zanen <jack@xxxxxxxxxxxx>
- Date: Wed, 03 Nov 2010 12:07:13 +0800
Hi Jack.
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);
--
//www.freelists.org/webpage/oracle-l
Other related posts:
