Re: update from two tables

From: Jerry D <jdubuke_at_notme.gpdservices.com>
Date: Thu, 1 Aug 2002 08:01:25 -0400
Message-ID: <3d492314$0$7545$4c41069e@reader0.ash.ops.us.uu.net>

OK - here is a snapshot of the relevant data...

IM Table - 15K rcds
IM_KEY (Unique) (varchar2,30)
IM_ALLOC (number,7,0)
{other non-relevant fields}

RQ Table - 168K rcds

RQ_IMKEY (varchar2,30)
RQ_QTY_REQ (number,7,0)
RQ_MOCNUMBER (varchar2,30)

{other non-relevant fields}

RQ Data
RQ_MOCNUMBER RQ_IMKEY RQ_QTY_REQ

A0111052                 ASLAN4        5
A0111052                 ASRAB1        8
A0111052                 RS325           3
A0111058                 ASLAN4        3
A0111058                 ASRAB1        6
A0111058                 RS325           2
A0111058                 SPAD32        8

{lots of other non-relevant data}

IM Data

IM_KEY                IM_ALLOC
ASLAN1                 32
ASLAN4                 27
ASRAB1                12
RS324                   15
RS325                   18
SPAD31                12
SPAD32                 4

{lots of other non-relevant data}

So, if I wanted to update for A0111058, I need IM_ALLOC to reflect the new data as follows:

IM_KEY                IM_ALLOC(old)     IM_ALLOC(new)
ASLAN4                 27                       30
ASRAB1                12                       18
RS325                   18                       20
SPAD32                 4                        12

So, I need the query to *only* update the 4 relevant records, and obviously a table scan gets a bit hairy with the size of my table(s).

Hope this helps - it should be soooo simple, no?? But it is driving me nuts!!

Jerry

{SNIP SNIP SNIP SNIP SNIP}
Received on Thu Aug 01 2002 - 07:01:25 CDT