Link to home
Start Free TrialLog in
Avatar of MeridianManagement
MeridianManagementFlag for United States of America

asked on

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

Avatar of Aneesh
Aneesh
Flag of Canada image

-- 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
Avatar of MeridianManagement

ASKER

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.
aneesh, it still says ambiguous column
ASKER CERTIFIED SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial