RE: Are these suppose to be equivalent?

From: Lex de Haan <lex.de.haan_at_naturaljoin.nl>
Date: Tue, 28 Sep 2004 21:23:22 +0200
Message-ID: <JFEEIGBIDOCCDALDIPLNAEFFCJAA.lex.de.haan@naturaljoin.nl>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Stephen.Lee_at_DTAG.Com Sent: Tuesday, September 28, 2004 17:06
To: oracle-l_at_freelists.org
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; / -- http://www.freelists.org/webpage/oracle-l

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

Received on Tue Sep 28 2004 - 14:19:21 CDT