SQL Update using MAX(Date)
===

SQL 2005

I need to update members records that haven't attended in the past 14 days. 
There are two tables: Members and Attendance. I need to update the member record with the most recent "Last_Attend_Date" if the Attend_Freq is less than 5 over the last 14 days. 

     Members table:
     ID     Attend_Freq     Last_Attend_Date
    123        4              2012-7-5  -- This is the result of the query, the most 
                                           recent date in the table instead of the 
                                           most recent for the member

     Attendance
     ID     Member_ID       Last_Attend_Date
     987      123               2012-6-5
     888      123               2012-6-4
     567      123               2012-6-3
     456      234               2012-6-30
    1909      292               2012-7-5

This is the query but its giving me the 'Subquery returned more than 1 value.' error instead of the most recent fro the member that I need to update. 

     UPDATE M
     SET Last_Attend_Date = 
     (SELECT Max(Last_Attend_Date) FROM Attendance A 
     JOIN Members M ON A.Member_ID = M.ID 
     WHERE A.Member_ID =M.id )
     FROM Members M
     JOIN Attendance A on A.Member_ID = M.id
     WHERE Attend_Freq <'5' and Last_Attend_Date <getdate()-14 and A.Member_ID = M.ID




