Re: update from two tables

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
=----- Received on Thu Aug 01 2002 - 14:07:47 CDT