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

update a
set a.nchange = 1
from bdgt_m a
     join (SELECT DISTINCT cacctid, bud_new
                  FROM bdgt_m_deleted
                  INNER JOIN (SELECT MAX(ddeleted) AS ddeleted
                                     FROM bdgt_m_deleted) Tbl1
           ON  bdgt_m_deleted.ddeleted = Tbl1.ddeleted) B
on a.cacctid = b.cacctid
where a.bud_new <> b.bud_new

?