Need clarification on Table variables
-
Hi All,
I have a quick question. While updating a column in table variable, the update is not going through if the syntax used is as follows...
UPDATE @MTD_TMP
SET NCH = isnull(@MTD_APP_TM.NCH, 0)
FROM @MTD_TMP JOIN @MTD_APP_TM
ON @MTD_TMP.LOG_DATE = @MTD_APP_TM.LOG_DATE
AND @MTD_TMP.RPT_GRID_NUM = @MTD_APP_TM.RPT_GRID_NUM
WHERE @MTD_APP_TM.LOG_DATE IS NOT NULL
BUT, it works fine with an alias. Something like this....
UPDATE @MTD_TMP
SET NCH = isnull(MAT.NCH, 0)
FROM @MTD_TMP MT JOIN @MTD_APP_TM MAT
ON MT.LOG_DATE = MAT.LOG_DATE
AND MT.RPT_GRID_NUM = MAT.RPT_GRID_NUM
WHERE MAT.LOG_DATE IS NOT NULL
COULD, anyone help me in clarifying the above behavior
Thanks in advance
-
I would expect that they had trouble with the @TableName.ColName syntaxe. The compiler probabely needs to assume that anything that begins with a @ is a parameter/variable unless it's right after the keywords ('select', 'Insert into', 'delete from'). But this more a guess than anything else.
-
Thanks for your contribution Remi. I appreciate it.
-
Thank you much Remi.
-
HTH.
I just wish that someone else with a more exact answer could post under this thread... Maybe someone from Teched?
-
It's a stated rule in BOL - you have to use aliases on table variables to refer to them in where clauses, etc. I've just tried finding it but I can't at the moment
In any case, it's a rule of the syntax. -
Look up KB296642.
Brian
MCDBA, MCSE+I, Master CNE
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply