RE: Are these suppose to be equivalent?
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: <Stephen.Lee@xxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 28 Sep 2004 21:23:22 +0200
I don't see much subquery magic here ...
you can probably rewrite the first update statement into an update against
an updatable join view:
(bear with me, I don't have the actual tables to test my syntax and check
for error messages ;-)
update (select p.loc_seq_no as p_loc_seq_no
, l.loc_seq_no as l_loc_seq_no
from planb_location p join location l
on (l.brand_id = p.brand_id and
l.loc_id = p.location_or_group )
)
set p_loc_seq_no = l_loc_seq_no;
this way you give the optimizer more possibilities, and I personally find
the statement easier to read.
Kind regards,
Lex.
-------------------------------
visit http://www.naturaljoin.nl
-------------------------------
skype me <callto://lexdehaan>
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Stephen.Lee@xxxxxxxx
Sent: Tuesday, September 28, 2004 17:06
To: oracle-l@xxxxxxxxxxxxx
Subject: Are these suppose to be equivalent?
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;
/
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
