Great.
That answers my question.
But on your CTE sample, I like that as well but am not sure why it works.
I made a small modification to your sample to add another column to #table2 that is the same name as in table1. As you said, it doesn't matter whether the table from the UPDATE statement is in either the FROM or the JOIN clauses.
In this scenario, I am updating t_txt. It only updates t_txt from #table1. What if I wanted to update that column from #table2? Why did it pick #table1?
IF OBJECT_ID('tempdb..#table1') IS NOT NULL DROP TABLE #table1;
IF OBJECT_ID('tempdb..#table2') IS NOT NULL DROP TABLE #table2;
CREATE TABLE #table1 (t_id int, t_txt varchar(20));
CREATE TABLE #table2 (t_id int, t_txt varchar(20));
INSERT #table1 VALUES (1,'old val'),(2,'old val'),(3,'old val');
INSERT #table2 VALUES (2, 'old val2'),(3,'old val2');
select * from #table1
select * from #table2
;WITH table1_filtered AS
(
SELECT t1.t_id, t1.t_txt
FROM #table1 t1
JOIN #table2 t2 ON t1.t_id = t2.t_id
)
UPDATE table1_filtered
SET t_txt = 'New Val'
select * from #table1
select * from #table2
Thanks,
Tom