multi row update in one SQL statment

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. Received on Fri Jul 25 2003 - 22:22:21 CEST