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
Received on Tue Sep 28 2004 - 11:01:59 CDT
