Re: update from two tables

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
  +----------------------------------------------------------------+