tshad (5/19/2014)
I was a little confused.I know you can only use whatever values are actually passed back (defined) from the CTE, I didn't know that the value remembers which row and table it came from.
I thought that:
UPDATE table1_filtered
SET t_txt = 'New Val'
I thought that the value passed back was just a value.
If you did something like:
SELECT t_id, t_txt
FROM
(
SELECT t1.t_id, t2.t_txt --This is changed to t2
FROM #table1 t1
JOIN #table2 t2 ON t1.t_id = t2.t_id
) a
The outside select doesn't actually know the tables and rows that t_id and t_txt actually come from - or does it?
Thanks,
Tom
Well in this case it doesn't really matter and is just a value as you said. However, remember that a cte is just like a view. And it does matter which table the values come from. The cte is more than just a resultset. This concept is why you can update a cte. 😉
The same logic applies here about updating with a view. The update statement cannot affect more than 1 base table.
Take a look at BOL about updateable views here. http://technet.microsoft.com/en-us/library/ms187956.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/