Hi,

Sorry but I am experiencing a brain freeze. I have two tables in SQL:

1. bdgt_m
cacctid (PK), nbudamt, ichange


2. bdgt_m_deleted
ddeldate, cacctid, nbudamt

There could be many records with the same cacctid in the bdgt_m_deleted table.

I am trying to write a statement to update ichange in the bdgt_m table if
bdgt_m.cacctid = bdgt_m_deleted.cacctid and a.nbudamt <> b.nbudamt. I only need one record from the bdgt_m_deleted table based on max(ddeleted) ?

This is what I got so far but it does not work:

update a
set a.nchange = 1
from bdgt_m a join bdgt_m_deleted b
on a.cacctid = (select distinct b.cacctid from bdgt_m_deleted b where a.cacctid = b.cacctid)
where a.bud_new <> b.bud_new