t-sql update from ambiguous column name

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
UPDATE account_master.dbo.accounts
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

Open in new window

LVL 2
MeridianManagementAsked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
i got you, its the accountid

UPDATE patients
SET patients.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 patients_status.AccountID = patients.AccountID ------------------this one
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
-- update dw modified field to indicate update in account
UPDATE patients
SET patients.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
0
 
MeridianManagementAuthor Commented:
just so you know, this procedure is being executed in a different database, so I can't just refer to the accounts table directly.

Secondly, there is no "patients" table.

The two tables involved here are

account_master.dbo.accounts (this is the one that needs to get updated)
copay.dbo.patients_status (this is the one with the extra information)

I want to update accounts, by joining status to accounts, and checking a value in status.
0
 
MeridianManagementAuthor Commented:
aneesh, it still says ambiguous column
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.