Q:

SQL server Update using JOIN

I used this command in Sql Server to update a table using the maximum data in other table innerjoin 
        UPDATE dbo.table2
        SET table2.LastGrantDate = max(table1.PlannedProjStartDate)
        from 
        table1 inner join table2
        on table2.Serial = table1.FundingEstablish

but it doesn't work 
it workes only when I use it without the MAX() but 
any way to solve this ??

A:

You need to use a subquery.  Here is one way:
    UPDATE t2
    SET t2.LastGrantDate = t1.maxppsd
    from table2 t2 inner join
         (select FundingEstablish, max(PlannedProjStartDate) as maxppsd
          from table1
          group by FundingEstablish
         ) t1
         on t2.Serial = t1.FundingEstablish;

