Re: update from two tables

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
> +----------------------------------------------------------------+
>
>
Received on Thu Aug 01 2002 - 13:39:54 CDT