tshad (5/19/2014)

---

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

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?