MeridianManagement
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.
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
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.account s (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.
Secondly, there is no "patients" table.
The two tables involved here are
account_master.dbo.account
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.
ASKER
aneesh, it still says ambiguous column
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
UPDATE patients
SET patients.date_dw_modified = CURRENT_TIMESTAMP
-- where patients were recently reviewed
FROM account_master.dbo.account
LEFT JOIN patients_status ON patients_status.accountid = patients.AccountID
WHERE patients_status.pending_re
and AccountID = patients.AccountID