November 3, 2008 at 9:45 am
Hi,
I would like to understand how the Alias works when you are using an Update Statement with the same table in the From Clause, it's a bit confusing for me... I think I got it right, but why doesn't the UPDATE ClAUSE for the table like to have an Alias?
Why I am asking is because is because I had SET in front of both columns to be updated producing this error; which lead me to think my Alias was off...
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'SD'.
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'SB'.
After fixing I get this Error;
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WHERE'.
So if someone could help explain my first question but also to see what I am doing to get my current error...
Thanks,
John
UPDATE ##Segement_Data2_JAS
SET
ELfactor = [XJASN4N].[fn_CALC_ELfactor](SD.ELfactor, M.EL, M.ELadjustment, SB.SegmentCriteriaBalance)
, ELcalc = [XJASN4N].[fn_CALC_ELcalc](SD.ELcalc, M.EL, M.ELadjustment, SD.Balance, SB.SegmentCriteriaBalance)
FROM tInputManual M
INNER JOIN ##Segement_Data2_JAS SD
ON M.MANUALID = SD.ConsumerSegmentCriteria
INNER JOIN
(
SELECT
G.ConsumerSegmentCriteria, SUM(G.SegmentCriteriaBalance) AS SegmentCriteriaBalance
FROM
(
SELECT
(CASE
WHEN S.SegmentID = 181 THEN 7
WHEN S.SegmentID = 182 AND ALR_RollupcompanyNbr <> '7976' THEN 146
WHEN S.SegmentID = 182 AND ALR_RollupcompanyNbr = '7976' THEN 147
WHEN S.SegmentID = 183 AND fldplevel03 = '102400000000' THEN 149
WHEN S.SegmentID = 183 AND fldplevel03 <> '102400000000' THEN 150
WHEN S.SegmentID = 184 THEN 151
WHEN S.SegmentID = 185 THEN 1
WHEN S.SegmentID = 186 AND fldplevel03 = '102400000000' THEN 125
WHEN S.SegmentID = 186 AND fldplevel03 <> '102400000000' THEN 126
WHEN S.SegmentID = 187 THEN 3
WHEN S.SegmentID = 188 THEN 148
WHEN S.SegmentID = 189 AND fldplevel03 = '102400000000' THEN 152
WHEN S.SegmentID = 189 AND fldplevel03 <> '102400000000' THEN 153
WHEN S.SegmentID = 190 THEN 154
WHEN S.SegmentID = 191 AND fldplevel03 = '102400000000' THEN 155
WHEN S.SegmentID = 191 AND fldplevel03 <> '102400000000' THEN 156
WHEN S.SegmentID = 192 AND fldplevel03 = '102400000000' THEN 157
WHEN S.SegmentID = 192 AND fldplevel03 <> '102400000000' THEN 158
ELSE NULL
END) AS ConsumerSegmentCriteria
, SUM(Balance) AS SegmentCriteriaBalance
FROM
##Segement_Data2_JAS S
GROUP BY
(CASE
WHEN S.SegmentID = 181 THEN 7
WHEN S.SegmentID = 182 AND ALR_RollupcompanyNbr <> '7976' THEN 146
WHEN S.SegmentID = 182 AND ALR_RollupcompanyNbr = '7976' THEN 147
WHEN S.SegmentID = 183 AND fldplevel03 = '102400000000' THEN 149
WHEN S.SegmentID = 183 AND fldplevel03 <> '102400000000' THEN 150
WHEN S.SegmentID = 184 THEN 151
WHEN S.SegmentID = 185 THEN 1
WHEN S.SegmentID = 186 AND fldplevel03 = '102400000000' THEN 125
WHEN S.SegmentID = 186 AND fldplevel03 <> '102400000000' THEN 126
WHEN S.SegmentID = 187 THEN 3
WHEN S.SegmentID = 188 THEN 148
WHEN S.SegmentID = 189 AND fldplevel03 = '102400000000' THEN 152
WHEN S.SegmentID = 189 AND fldplevel03 <> '102400000000' THEN 153
WHEN S.SegmentID = 190 THEN 154
WHEN S.SegmentID = 191 AND fldplevel03 = '102400000000' THEN 155
WHEN S.SegmentID = 191 AND fldplevel03 <> '102400000000' THEN 156
WHEN S.SegmentID = 192 AND fldplevel03 = '102400000000' THEN 157
WHEN S.SegmentID = 192 AND fldplevel03 <> '102400000000' THEN 158
ELSE NULL
END)
) G
WHERE
G.ConsumerSegmentCriteria IS NOT NULL
GROUP BY
G.ConsumerSegmentCriteria
) SB
WHERE M.ManualType = 'Consumer'
AND SD.SUBTRACTION <> 1
November 3, 2008 at 10:51 am
I got the error, forgot to join the other table... doh
November 3, 2008 at 10:53 am
Since you're aliasing the table - I think you need to start with using the alias in the top of the UPDATE statement, as in:
UPDATE SD
Since you can only update one table per UPDATE statement, you can't use a prefix on the "left" side of the SET clauses, but you can everywhere else. I however think it's simply because of the above issue (the other messages should go away once you switch to the alias up top).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply