Re: Update query with another table specified in where clause

I'm not familiar with FROM clause in updates. update <tablename> set <fieldname> = <value> FROM ....

But the following code should be close.

update counterparty
set counterparty.cnt_validto = 20020730
where exists
(

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

      counterparty.validfrom = old_version.minvalidfrom and
      oldversion.nbrecords > 1

Chris Wilson Received on Thu Aug 01 2002 - 18:19:08 CEST