How do you update a joined table in SQL SERVER?

Hi all,

I been trying for days to figure this one out and can't seem to get the task done.

I have two tables in SQL SERVER 2005.  Table 1 is tblRecords and table 2 is tblRecords_appts and I need to join them to get be able to update the right records.

I need to update all records that have wedding dates that have passed and set canceled to yes.  

So I figured the correct update statement would be:

UPDATE tblRecords_appts INNER JOIN
                      tblRecords ON tblRecords_appts.rid = tblRecords.rid
SET tblRecords_appts.cancelled = 'Y'    
WHERE     (tblRecords.weddate < '10/1/2013') AND (tblRecords_appts.locationid = '17') AND (tblRecords_appts.cancelled = 'N')

Select allOpen in new window

but obviously that is not working :(

I'm pretty sure it has to be an additional select under where.  Any help would be appreciated!

Thanks,
Matt