Re: multi row update in one SQL statment

"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
                                     )