Update from CTE but the multi-part identifier could not be bound

Question

I'm facing an issue and I need your help. What I'm trying to do is update a value using a CTE.

I've created my CTE with using the script below:

;WITH CTE AS
(
    SELECT TOP 1 
        TABLE1.objectid AS [carrier_object_id], cardNo 
    FROM 
        TABLE1
    INNER JOIN 
        TABLE2 ON TABLE1.objectid = TABLE2.carrierobjectid
    ORDER BY 
        cardNo DESC
)

After that I'm trying to write the update with an update query like below:

UPDATE [db].[dbo].[freefieldassignment]
SET [db].[dbo].[freefieldassignment].value = CAST((CTE3.cardNo) + 1 AS VARCHAR(300))
WHERE [db].[dbo].[freefieldassignment].[carrierobjectid] = '90465607'

As you can see I'm trying to set the cte's value to the freefiledassignment.value but an error pops up when I execute the script:

The multi-part identifier "CTE.cardNo" could not be bound.

Could anyone tell me what I'm missing on this situation because I'm trying to figure it out but I can't. Thank you for your time.

Answer 1

You have to do join WITH CTE :

UPDATE A
      SET A.value = CAST(C.cardNo + 1 AS varchar(300))
FROM [db].[dbo].[freefieldassignment] A INNER JOIN
     CTE C
     ON C.<COL> = A.<COL>
WHERE A.[carrierobjectid] = '90465607';

However, if you want to set common value (return by cte) then you can also use variable :

DECLARE @cardNo VARCHAR(255)

SELECT TOP (1) @cardNo = cardNo 
FROM TABLE1 INNER JOIN 
     TABLE2 
     ON TABLE1.objectid = TABLE2.carrierobjectid
ORDER BY cardNo DESC;

UPDATE A
      SET A.value = CAST(@cardNo + 1 AS varchar(300))
FROM [db].[dbo].[freefieldassignment] A 
WHERE A.[carrierobjectid] = '90465607';