Re: Are these suppose to be equivalent?

It would appear that statement #2 should update a subset of the data
that statement #1 is updating.

One exception would be if there is a 1:1 relation between location
and planb_location.


Jared


On Tue, 28 Sep 2004 11:06:08 -0500, stephen.lee@xxxxxxxx
<stephen.lee@xxxxxxxx> wrote:
> 
> On the tables I have here, in a 9.2.0.4 database, the following produce the
> same update.  The question is: Should they?  That is, is this something the
> SQL spec says is supposed to work this way, or is this just some more
> subquery magic being applied by the Oracle optimizer.  The intent is to
> update a column in one table (planb_location) with the corresponding values
> from another table (location).
> 
> -- statement #1 (is this Oracle subquery magic, or is this genuine SQL spec
> OK?)
> Update planb_location set loc_seq_no = (select l.loc_seq_no from location l
> where l.brand_id = planb_location.brand_id and l.loc_id =
> planb_location.location_or_group);
> 
> -- statement #2
> declare
>     cursor c1 is select l.loc_seq_no from location l, planb_location p where
> l.brand_id = p.brand_id and l.loc_id = p.location_or_group for update of
> p.loc_seq_no;
> begin
>    for i in c1 loop
>       update planb_location set loc_seq_no = i.loc_seq_no where current of
> c1;
>    end loop;
> end;
> /
> --
> http://www.freelists.org/webpage/oracle-l
> 



-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
--
http://www.freelists.org/webpage/oracle-l

Other related posts: