SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table foo (
  id int primary key,
  a varchar(10)
);
create table bar (
  id int primary key,
  a varchar(10)
);
insert foo values
(1,'a'),
(2,'b');
insert bar values
(1,'x'),
(2,'y');
4 rows affected

WITH cte AS (
	SELECT
	    f.a,
	    b.a AS new_a,
	    '?' AS something_else
	FROM foo f
	JOIN bar b ON b.id = f.id
)
UPDATE cte SET
    a += something_else,
    new_a += something_else;
Msg 4405 Level 16 State 1 Line 1
View or function 'cte' is not updatable because the modification affects multiple base tables.

SELECT * FROM foo;


id
a


1
a

2
b


SELECT * FROM bar;


id
a


1
x

2
y