An ugly self-join (oh, that doesnt work!)

Hi again, all...here I am again, trying to work on my CloseIndex thang again...Same subject, different tack...

Basically, I have two tables...for the sake of simplicity, let me define them as:

PortfolioIndex
PortfolioID int
CreateDate smalldatetime
CloseIndex float

PortfolioPerformance
PortfolioID int
CreateDate smalldatetime
PrevDate smalldatetime
DailyPerChg float

UPDATE PortfolioIndex
SET CloseIndex = CASE
WHEN PPI.CloseIndex IS NULL THEN 100.00
ELSE (PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100))
END
FROM PortfolioIndex AS P INNER JOIN PortfolioIndex AS PPI on (P.PortfolioID = PPI.PortfolioID), PortfolioPerformance AS PP
WHERE (P.PortfolioID = PP.PortfolioID) AND
((P.CreateDate = PP.CreateDate) AND
(P.CreateDate = @.CreateDate) AND
(PPI.CreateDate = PP.PrevDate))

What I am trying to do is...get the previous day's portfolioIndex row's CloseIndex and create a new one for today's row.

As ugly as it is, it works when I execute it in the SQL Query Analyzer, but when I try to create the stored procedure, the syntax check complains that the PortfolioIndex reference at the UPDATE... part is AMBIGUOUS...yet when I define it in the SP as P.PortfolioIndex, it fails at run time saying there is no object named P.PortfolioIndex (well, of course there isn't!).

How can I make this work (and if possible, make it prettier too! *L* ;) )well, now I see it doesn't really work in the sql analyzer either...but did on a previous iteration (before I added the inner join).

Still, the idea/question is the same...is there a less kludg-ey way to do the self-join to get the previous day's row and update the new row based on data from the old and the 2nd (PortfolioPerformance) table?

Thanks,
Paul|||select /*PI.CloseIndex = */ PI.PortfolioID, CASE
WHEN PPI.CloseIndex IS NULL THEN 100.00
ELSE (PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100))
END
from PortfolioIndex PI, PortfolioIndex PPI, PortfolioPerformance PP
where ((PI.PortfolioID = PPI.PortfolioID AND
PI.PortfolioID = PP.PortfolioID))AND
((PI.CreateDate = PP.CreateDate) AND
(PI.CreateDate = @.CreateDate) AND
(PPI.CreateDate = PP.PrevDate))

It worked this way in the SQL Analyzer...when I was testing without the update...so...how can I reference the table in an update without being ambiguous? I can't seem to find any example code anywhere about updates with self-joins...can it be done?

Thanks,
Paul|||Update table
set column = (select ...)|||Thanks...*hanging head*

It must be time to go home...

Thanks for taking the time to point me in the direction of the forest once again...|||I've been trying to get the select correct as suggested, but can't get it to work for me.

Possibly because one requirement is not plain from my previous description...

There will be multiple PortfolioIndex rows there each day (since there will be more than one PortfolioID on each day).

Modifying my update as suggested results in multiple results being returned from the sub-query, which I cannot figure out how to apply to each individual PortfolioIndex row! *grrrr*

Here is my modified query:

DECLARE @.CreateDate smalldatetime
DECLARE @.PrevDate smalldatetime
SET @.CreateDate = '2004-02-13'
SET @.PrevDate = '2004-02-12'

Update PortfolioIndex
Set CloseIndex = (
select CASE
WHEN PPI.CloseIndex IS NULL THEN 100.00
ELSE (PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100))
END
from PortfolioIndex PPI, PortfolioPerformance PP
where (PPI.PortfolioID = PP.PortfolioID)AND
(PP.CreateDate = @.CreateDate) AND
(PPI.CreateDate = @.PrevDate))

The result is:
Server: Msg 512, Level 16, State 1, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Any helpful suggestions?|||anyone? I know I must be missing something obvious, but can't see it...
how does one update a table joined to itself in the select clause?

Originally posted by TallCowboy0614
I've been trying to get the select correct as suggested, but can't get it to work for me.

Possibly because one requirement is not plain from my previous description...

There will be multiple PortfolioIndex rows there each day (since there will be more than one PortfolioID on each day).

Modifying my update as suggested results in multiple results being returned from the sub-query, which I cannot figure out how to apply to each individual PortfolioIndex row! *grrrr*

Here is my modified query:

DECLARE @.CreateDate smalldatetime
DECLARE @.PrevDate smalldatetime
SET @.CreateDate = '2004-02-13'
SET @.PrevDate = '2004-02-12'

Update PortfolioIndex
Set CloseIndex = (
select CASE
WHEN PPI.CloseIndex IS NULL THEN 100.00
ELSE (PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100))
END
from PortfolioIndex PPI, PortfolioPerformance PP
where (PPI.PortfolioID = PP.PortfolioID)AND
(PP.CreateDate = @.CreateDate) AND
(PPI.CreateDate = @.PrevDate))

The result is:
Server: Msg 512, Level 16, State 1, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Any helpful suggestions?|||The error say it all...how can you update a column with n results...you need to make sure the query only returns 1
per row

Forget the update and just focus on the select to make sure it's returning what you need.sql