Re: multi row update in one SQL statment

"Nicolas Payre" <nicpayre[junk]_at_sympatico.ca> wrote in message news:<TTlUa.8878$1I5.1134904_at_news20.bellglobal.com>...
> "Rima" <parikhrima_at_yahoo.com> wrote in message
> news:8a126d63.0307251222.2b11147e_at_posting.google.com...
> > I have the following two tables :
> >
> > table a
> > (commit_id,
> > capital_market_id,
> > chg_lst_date
> > )
> >
> > table b
> > (b_seq_id,
> > commit_id,
> > capital_market_id,
> > chg_lst_date
> > )
> >
> > commid_id is PK in A but not in B. B can have multiple entries per
> > commit_id.
> >
> > I want to update all entries in table A - set the capital_market_id
> > and chg_lst_date - from the corresponding commit_id entry in table B
> > with the following two rules :
> >
> > 1. since B can have multiple rows per commit_id I want to pick the one
> > with the latest txn_time and
> > 2. only update in A if the chg_lst_date of a is less than that of B.
> >
> > Can I do this in one update statement? or do I have to do a
> > cursor/loop ?
> >
> > I originally posted this in oracle.misc but then realized it might be
> > a non-technical forum - hence this repeat post.
>
> update A a
> set (a.commit_id, a.capital_market_id, a.chg_lst_date) = (
> select b.commit_id, b.capital_market_id, b.chg_lst_date
> from B b
> where b.chg_lst_date = ( select max(b1.chg_lst_date)
> from B b1
> where b1.commit_id =
> b.commit_id
> )
> )
> where a.chg_lst_date < (select max(b2.chg_lst_date)
> from B b2
> where b2.commit_id = a.commit_id
> )

Thanks Nicolas.

I'm a little confused however, according to the docs in the case of

update ... set (col,col,col...) = (subquery)

the subquery must return exactly one row.

your subquery :

select b.commit_id, b.capital_market_id, b.chg_lst_date from B b
where b.chg_lst_date = ( select max(b1.chg_lst_date)

                           from B  b1
                          where b1.commit_id = b.commit_id
                       )

Thanks,
Rima. Received on Mon Jul 28 2003 - 20:59:03 CEST