Re: Update with SQL Question

From: <rtproffitt_at_my-deja.com>
Date: Wed, 30 Jun 1999 17:01:45 GMT
Message-ID: <7ldihh$jol$1_at_nnrp1.deja.com>

You can eliminate one of the correlated
subselects by using ROWID to your advantage. I also assume that in your xref table you have an index on OLD_EPI_NUM.

Here is your code rewritten. After that, I show my sample case.

UPDATE SX_TABLE SX
    SET MPI_ID = (SELECT NEW_EPI_NUM

          FROM MPI_XREF
          WHERE MPI_XREF.OLD_EPI_NUM = SX_TABLE.MPI_ID)
    WHERE SX.ROWID IN
       (SELECT sxb.ROWID
        FROM SX_TABLE SXB, MPI_XREF REF
        WHERE
        SXB.MPI_ID = REF.OLD_EPI_NUM);
        REF.OLD_EPI_NUM = SX_TABLE.MPI_ID);

Although you still need the correlated subselect for the SET statement, you can eliminate the second one, since a JOIN will return the same result as EXISTS in your case. Access by ROWID is generally very fast.

My test case is listed here below. I created two tests, one in which more cases were in xref than in SX table, and the one shown, where more cases exist in the SX table than in the xref.

create table bobsx
  (mpi_id number);
create table bobxref

   (oldnum number,
    newnum number);

insert into bobsx values ( 1);
insert into bobsx values ( 2);
insert into bobsx values ( 3);
insert into bobsx values ( 4);
insert into bobsx values ( 5);
insert into bobsx values ( 6);
insert into bobsx values ( 7);
insert into bobsx values ( 8);
insert into bobsx values ( 9);
insert into bobsx values (10);
insert into bobsx values (11);
insert into bobsx values (42);
insert into bobsx values (43);
insert into bobsx values (44);
insert into bobsx values (45);

insert into bobxref values ( 1,11);
insert into bobxref values ( 3,13);
insert into bobxref values ( 5,15);
insert into bobxref values ( 7,17);

insert into bobxref values ( 9,19);
insert into bobxref values (11,21);
commit;

• check ourselves. select * from bobsx; select * from bobxref;

Update Bobsx

   set mpi_id = (select newnum

      from bobxref
      where bobxref.oldnum = bobsx.mpi_id)
   where rowid in
      (select sx.rowid
       from bobsx sx, bobxref ref
       where sx.mpi_id = ref.oldnum);

Good Luck
Robert Proffitt
Beckman Coulter
Brea California
RTProffittNOSPAM_at_Beckman.com

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Jun 30 1999 - 19:01:45 CEST