Re: Update query with another table specified in where clause

Hi Chris,

In the mean time we've found the query below which is nearly the same as yours. The only difference is that we are selecting * instead of 1 in the first subquery. Can this affect performance ? Anyway, thanks a lot for your suggestion. It confirms our solution.

 update counterparty
 set counterparty.cnt_validto = 20020730  where exists
(

 select * 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