Re: update from two tables

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 - 13:54:11 CDT