Use CTE for update records

I have 2 table1 and table2 and want to update data table 2 from table 1 using CTE

table1

table2

The expect result for table2 after the update:

My CTE

With cteupdate as 
(Select Id, Name, class
from table1 t1
join table2 t2
on t1.Id = t2.Id)
Update cteupdate set t2.class = t1.class

Returns this error:

Update or insert of view or function 'cteupdate' failed because it contains a derived or constant field.

Solution 1:

When both tables have columns with the same name, I don't know that you're going to be able to do that (in fact I'm surprised you didn't get dinged with an ambiguous column name error). How about:

UPDATE t2 SET t2.class = t1.class
  FROM dbo.table2 AS t2
  INNER JOIN dbo.table1 AS t1
  ON t1.Id = t2.Id
  WHERE t1.class IS NOT NULL;

• Example db<>fiddle

I'm not sure why it's so important to use a CTE, and this might be harder for future maintainers to understand why you want this roundabout approach too, but perhaps:

;WITH cteupdate AS
(
  SELECT t2.Id, t2.class, newclass = t1.class
    FROM dbo.table1 AS t1
    INNER JOIN dbo.table2 AS t2
    ON t1.Id = t2.Id
    WHERE t1.class IS NOT NULL
)
UPDATE cteupdate SET class = newclass;

The main problem (aside from ambiguous column names, which I address by applying a different alias to the "new" class column), is that you can't reference t1/t2 outside of the CTE, since all that's left at that point is the CTE.

• Example db<>fiddle