Re: Update not behaving as hoped

From: Jack van Zanen <jack_at_vanzanen.com>
Date: Wed, 3 Nov 2010 15:31:13 +1100
Message-ID: <AANLkTikPDRKeQYA4cw580U6mN2UuJ9PDS4sg0-3Rq7mT_at_mail.gmail.com>

Thanks for all the replies.

lessons learned

Jack van Zanen

---

This e-mail and any attachments may contain confidential material for the sole use of the intended recipient. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of this e-mail or any attachment is prohibited. If you have received this e-mail in error, please contact the sender and delete all copies. Thank you for your cooperation

On Wed, Nov 3, 2010 at 3:07 PM, dbvision_at_iinet.net.au <dbvision_at_iinet.net.au
> wrote:

> 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);
>
>
>
>

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

Received on Tue Nov 02 2010 - 23:31:13 CDT