Update query using join errors as multipart identifier could not be bound-CodePudding

Can someone please point out to me where I am doing wrong here? I am trying to update the temp table by joining the ABCD table by a condition(@USEAB variable). In some of our customer databases, the ABCD table doesn't exist hence the variable returns Y or N.

DECLARE
@USEAB NVARCHAR(2) = 'N'

DECLARE @TEMPTABLE TABLE (REGID NVARCHAR(8), REF NVARCHAR(6), ITEM INT, PRD NVARCHAR(6), SOURCE NVARCHAR(2), TRANID NVARCHAR(10))

IF @USEAB = 'Y' BEGIN
    --OBTAINING CASH TYPE THROUGH TRANID
    UPDATE @TEMPTABLE
    SET TEMP.REGID = AB.REGID, TEMP.REF = CASE WHEN AB.PRD IS NULL THEN 'INVOICE' ELSE  'CASH' END, TEMP.PRD = AB.PRD
    FROM @TEMPTABLE TEMP
    INNER JOIN ABCD AB
    ON AB.TRANID = TEMP.TRANID
    WHERE TEMP.SOURCE =  N'AB'
    AND TEMP.TRANID IS NOT NULL
END

I got 'The multi-part identifier TEMP.REGID could not be bound'.

CodePudding user response：

You gave your @TEMPTABLE the alias TEMP. You may not refer to it with its original name anymore. Change

Update @TEMPTABLE

Update TEMP