I'm trying to mass update the account table using another joined table which indicates whether the account had been recently reviewed.
If so, then I want to update the account table with today's date.
The problem is that since I'm updating into itself, the primary key is the same, and since you can't use alias in updates, I'm getting ambiguous column on the very last line.
-- update dw modified field to indicate update in account
SET date_dw_modified = CURRENT_TIMESTAMP
-- where patients were recently reviewed
FROM account_master.dbo.accounts patients
LEFT JOIN patients_status ON patients_status.accountid = patients.AccountID
WHERE patients_status.pending_review = 1
and AccountID = patients.AccountID