Re: update from two tables

No Prob, it was a nice challenge..
Create a compound index on rq (rq_imkey,rq_mocnumber) [ if you do, be sure to refer to them IN THAT ORDER to be sure the index is used] so Replace the
 where im_key = (select rq_imkey from rq    where rq_mocnumber = 'A0111058' and rq_imkey = imkey); with
 where im_key = (select rq_imkey from rq    where rq_imkey = imkey and rq_mocnumber = 'A0111058');

and
also
index ( if not already) im_key in im

"Jerry D" <jdubuke_at_notme.gpdservices.com> wrote:

>HOORAY!
>It worked, but took 22 secs to update 6 rows, and the bad news is that there
>are some cases where the combination of the 2 is not unique - (sorry - i
>misunderstood your last post - thought you were asking if there were any
>RQ_IMkey entries the same as RQ_MOCNUMBER...)
>
>But now I have something that at least works, and I can try to figure out
>how to speed it up, since I have to do this for every order (which could be
>significant).
>
>Thanks soooooooooooo much for hte help!!
>
>Jerry
>
>"TurkBear" <jgreco1_at_mn.rr.com> wrote in message
>news:fj0jku4s8jhp3vun8lemgf7but9vasuvbg_at_4ax.com...
>>
>> OK, now lets see what happens with this :
>>
>> update im set im_alloc = (select im_alloc + rq_qty_req
>> from rq where rq_imkey = imkey and rq_mocnumber='A0111058')
>> where im_key = (select rq_imkey from rq
>> where rq_mocnumber = 'A0111058' and rq_imkey = imkey);
>>
>> Worth a try - the logic being to use the same selection criteria for both
>queries.
>> If rq_imkey combined with rq_mocnumber creates a unique identifier for 1,
>and only 1, row in rq, then it should work..
>>
>>
>> "Jerry D" <jdubuke_at_notme.gpdservices.com> wrote:
>>
>> >Glad to see another perosn involved - this is getting good...
>> >
>> >Well, I had already tried something very similar to that and get.....
>> >ORA-01427 - single-row subquery returns more than one row
>> >
>> >because the outer where (last one) would return 5 rows in this case, and
>> >even thought we specified the "in" clause, it doesnt like it...
>> >
>> >"Phil Huber" <phil_at_mtu.edu> wrote in message
>> >news:3D497662.B5B1F433_at_mtu.edu...
>> >> Jerry,
>> >>
>> >> It's kind of messy, but I think that this is what you need:
>> >>
>> >> update im set im_alloc = (select im_alloc + rq_qty_req
>> >> from rq where rq_imkey = imkey)
>> >> where im_key in (select rq_imkey from rq
>> >> where rq_mocnumber = 'A0111058')
>> >> /
>> >>
>> >> Good luck,
>> >> Phil
>> >>
>> >> Jerry D wrote:
>> >>
>> >> > you are missing nothing! That is the problem, laughing...
>> >> >
>> >> > I could not figure it out either...If you look at the orig post, it
>> >shows a
>> >> > simple select statement that gets me the data I want, but I cant
>write
>> >an
>> >> > update statement in oracle to handle it. I know I can do it in
>access,
>> >or
>> >> > sybase (not sure about SQL server), but not in oracle.
>> >> >
>> >> > I got around it by doing the updates on a 1-by-1 basis from an ado
>> >> > recordset, but I am still convinced that there is a simple, fast
>update
>> >> > query that would work - just haven't found it yet!
>> >> >
>> >> > Jerry
>> >> >
>> >> > "Tim C" <NOTCornwell_at_NOTcs.NOTcornell.NOTedu> wrote in message
>> >> > news:aibqt4$hv8$1_at_news01.cit.cornell.edu...
>> >> > > Jerry D,
>> >> > >
>> >> > > Did you ever get this resolved??
>> >> > >
>> >> > > I have what seems to be the exact requirement, but cannot find any
>> >> > examples
>> >> > > of howthis might be done.
>> >> > >
>> >> > > I don't have much experience with Oracle, but this seems like it
>> >should be
>> >> > > so very simple:
>> >> > >
>> >> > > Table_A has field_X
>> >> > > Table_B has fieldY
>> >> > >
>> >> > > BOTH tables have key value Z
>> >> > >
>> >> > > Find where the (unique) key values from A are in B, and update
>field_X
>> >in
>> >> > A
>> >> > > to be some kind of product of the tableA and TableB fields.
>> >> > >
>> >> > > Sounds easy. I think Access & SQL Server can do this easily.
>> >> > >
>> >> > > What am I missing?
>> >> > >
>> >> > > Tim
>> >> > >
>> >> > >
>> >> > >
>> >> > >
>> >> > > "Jerry D" <jdubuke_at_notme.gpdservices.com> wrote in message
>> >> > > news:3d480aec$0$397$724ebb72_at_reader2.ash.ops.us.uu.net...
>> >> > > > Hi all,
>> >> > > > I have read past posts on this, but I cannot "fit" it to my
>needs -
>> >> > > > I have 2 tables (im and rq) - the relationship is rq_imkey =
>im_key.
>> >> > > > I need to update the im_alloc field as follows:
>> >> > > > im_alloc = im_alloc + rq_qty_req
>> >> > > > where the rq_mocnumber = 'xxxxx'.
>> >> > > >
>> >> > > > The select statement is the easiest to describe what I need:
>> >> > > >
>> >> > > > select im_key, im_alloc, rq_qty_req from up.im, up.rq where
>> >rq_mocnumber
>> >> > =
>> >> > > > 'A0111058' and im_key = rq_imkey;
>> >> > > >
>> >> > > > This query will generate the resultset I need, now I need to
>update
>> >the
>> >> > IM
>> >> > > > table with the above values!
>> >> > > >
>> >> > > > Dont know why I have such a block with this, can anyone help??
>> >> > > >
>> >> > > > Jerry
>> >> > > >
>> >> > > >
>> >> > > >
>> >> > >
>> >> > >
>> >>
>> >> --
>> >> +----------------------------------------------------------------+
>> >> Phillip Huber Database Administrator phil_at_mtu.edu
>> >> Michigan Technological University (906)487-2224
>> >> +----------------------------------------------------------------+
>> >>
>> >>
>> >
>>
>>
>>
>> -----------== Posted via Newsfeed.Com - Uncensored Usenet News
>==----------
>> http://www.newsfeed.com The #1 Newsgroup Service in the World!
>> -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers
>=-----
>

-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------

   http://www.newsfeed.com The #1 Newsgroup Service in the World! -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =----- Received on Thu Aug 01 2002 - 14:33:40 CDT