>update a >set a.nchange = 1 >from bdgt_m a > join (SELECT DISTINCT bdgt_m_deleted.cacctid, bud_new, bdgt_m_deleted.ddeleted > FROM bdgt_m_deleted > INNER JOIN (SELECT cacctid, MAX(ddeleted) AS ddeleted > FROM bdgt_m_deleted GROUP BY cacctid) Tbl1 > ON bdgt_m_deleted.ddeleted = Tbl1.ddeleted) B >on a.cacctid = b.cacctid >where a.bud_new <> b.bud_new >No, not good. The change you made changes the statement completely and you will never be sure about the result. Because you JOIN table ONLY by Date.
update a set a.nchange = 1 from bdgt_m a join (SELECT DISTINCT bdgt_m_deleted.cacctid, bud_new, bdgt_m_deleted.ddeleted FROM bdgt_m_deleted INNER JOIN (SELECT cacctid, MAX(ddeleted) AS ddeleted FROM bdgt_m_deleted GROUP BY cacctid) Tbl1 ON bdgt_m_deleted.cacctid = Tbl1.cacctid AND bdgt_m_deleted.ddeleted = Tbl1.ddeleted) B on a.cacctid = b.cacctid where a.bud_new <> b.bud_newThat way you will get MAX() date for every cacctid.