Multi row update in one SQL statement

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
)

commit_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 chg_lst_date 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 ? Received on Fri Jul 25 2003 - 11:56:02 CDT