#TAble is ambiguous

Question

I am getting an error that states #Plans is ambiguous. This happens when I am joining a table to itself on a self join and am not sure why. Here is the code that leads to the error:

Alter Table #Plans
Add SecondPlanDate date

Update #Plans
Set 
    SecondPlanDate = Min (P2.PlanPurchaseDate) Over (Partition By P1.PatientID, P1.PlanPurchaseDate)
From
#Plans as P1
Inner Join
#Plans as P2
on
P1.PatientID = P2.PatientID
Where
P2.PlanPurchaseDate > P1.PlanPurchaseDate
;

Select
*
From
#Plans

Any suggestions would be greatly appreciated.

Thanks,

score 5 · Answer 1 · answered May 08 '14 at 23:51

5

I think you need to use the table alias:

UPDATE P1
SET P1.SecondPlanDate ...

SQL Fiddle Demo

answered May 08 '14 at 23:51

sgeddes

62,311
6
61
83

score 1 · Answer 2 · answered May 08 '14 at 23:52

1

Modify query to

Update P1
Set 
    P1.SecondPlanDate = Min (P2.PlanPurchaseDate) Over (Partition By P1.PatientID, P1.PlanPurchaseDate)
From
#Plans as P1

answered May 08 '14 at 23:52

qxg

6,955
1
28
36

score 0 · Answer 3 · answered May 09 '14 at 04:15

0

Try this:

UPDATE #Plans
SET SecondPlanDate = P2.FirstPlanPurchaseDate
FROM #Plans P1
INNER JOIN ( SELECT PatientID,
                    MIN(PlanPurchaseDate) AS FirstPlanPurchaseDate
             FROM #Plans
             GROUP BY PatientID) P2
ON P1.PatientID = P2.PatientID

answered May 09 '14 at 04:15

Nithin Gangadharan

527
4
9

Actually- Doesn't quite work. Sorry- only pulls the earliest date, not looking for the earliest date. Looking for earliest date in P2 that is greater than P1's date. – SASUSMC May 09 '14 at 16:22

score 0 · Answer 4 · edited Mar 05 '15 at 06:20

0

Update #Plans
Set 
SecondPlanDate = <your value>
From
#Plans , #Plans  P2
Where
#Plans.PatientID = P2.PatientID
and P2.PlanPurchaseDate > #Plans.PlanPurchaseDate
//ie the solution is to reference the update table in the join as well

edited Mar 05 '15 at 06:20

Syed Ali Salman

2,894
4
33
48

answered Mar 05 '15 at 05:43

rubaina

1

#TAble is ambiguous

4 Answers4