Use STE for update records

January 22, 2022

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

table1
id   name class  
1    a     xxx
2    b     vvv 
3    c     eee

table2
id   name  class
1    a     xxx
2    b
3    c

The expect result for table2

id  name  class
1   a     xxx
2   b     vvv
3   c     eee

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

Got error

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

Not sure how to correct it. Thank you

>Solution :

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.