?
Solved

t-sql update from ambiguous column name

Posted on 2010-01-05
4
Medium Priority
?
745 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:MeridianManagement
  • 2
  • 2
4 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26182270
-- 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
 
LVL 2

Author Comment

by:MeridianManagement
ID: 26182485
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
 
LVL 2

Author Comment

by:MeridianManagement
ID: 26182685
aneesh, it still says ambiguous column
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 26182699
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question