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_at_dtag.com <stephen.lee_at_dtag.com> 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