Update query with another table specified in where clause

Hi,

I've problem porting this SQL Server 2000 query to Oracle 9i. The complexity, I think, resides in the fact that the where clause of the update is using SEVERAL columns of the old_version table (I've found some examples with one column only).

update counterparty
set counterparty.cnt_validto = 20020730
from (select cnt_id, min(cnt_validfrom) as minvalidfrom, sum(1) as nbrecords from counterparty where cnt_validto is null group by cnt_id) as old_version
where counterparty.cnt_id = old_version.cnt_id and

      counterparty.cnt_validfrom = old_version.minvalidfrom and
      old_version.nbrecords > 1

PS: cnt_id is not unique (we have historical versions of counterparties in this counterparty table)

Thanks in advance. Received on Wed Jul 31 2002 - 19:11:36 CEST