Your update query should be fine and, no, it does not matter if you include the table in the FROM or table operator clauses (e.g. JOIN & APPLY).

With the way that SQL Server evaluates your query, the UPDATE statement is actually evaluated after the FROM and JOIN clauses. As long as your target table and target column are listed in you FROM or JOIN clauses they can be referenced in your SELECT or UPDATE statements.

Itzek Ben-Gan talks about the query processing order in his excellent books. This pdf is a good reference:

I like this method because it's easier to understand and debug. Both UPDATES will produce the same query plan but, with the CTE method you can quickly highlight and run your select statement to see ahead of time what rows will be affects.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001

Alan.B (5/18/2014)Your update query should be fine and, no, it does not matter if you include the table in the FROM or table operator clauses (e.g. JOIN & APPLY).

With the way that SQL Server evaluates your query, the UPDATE statement is actually evaluated after the FROM and JOIN clauses. As long as your target table and target column are listed in you FROM or JOIN clauses they can be referenced in your SELECT or UPDATE statements.

Itzek Ben-Gan talks about the query processing order in his excellent books. This pdf is a good reference:

I like this method because it's easier to understand and debug. Both UPDATES will produce the same query plan but, with the CTE method you can quickly highlight and run your select statement to see ahead of time what rows will be affects.

Cheers.

This is seriously a top forum answer. Great link to the Itzik chart, plus I did not know you could update with CTE like that. 5 stars.

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;

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;

tshad (5/19/2014)I also tried it by taking the t1.t_id off of the selection list and it still worked.

You said it knows about the column from the one table but not the other table.

I guess I am a little confused here. I thought that the update would only know about the result set and not the underlying tables or tables the columns come from.

Is this not the case?

Because when I removed the ID and it know which record to update as well as the table it came from, I was a little confused.

Thanks,

Tom

When you use a cte as the target of an update it is just like updating a view. It only can see the columns defined. Since you defined the cte to use t1.t_txt it will update the value in #table1. If you change the cte and NOT the update it will update #table2.

IF OBJECT_ID('tempdb..#table1') IS NOT NULL DROP TABLE #table1;IF OBJECT_ID('tempdb..#table2') IS NOT NULL DROP TABLE #table2;

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.

Alan.B (5/18/2014)Your update query should be fine and, no, it does not matter if you include the table in the FROM or table operator clauses (e.g. JOIN & APPLY).

With the way that SQL Server evaluates your query, the UPDATE statement is actually evaluated after the FROM and JOIN clauses. As long as your target table and target column are listed in you FROM or JOIN clauses they can be referenced in your SELECT or UPDATE statements.

Itzek Ben-Gan talks about the query processing order in his excellent books. This pdf is a good reference:

I like this method because it's easier to understand and debug. Both UPDATES will produce the same query plan but, with the CTE method you can quickly highlight and run your select statement to see ahead of time what rows will be affects.

Cheers.

This is seriously a top forum answer. Great link to the Itzik chart, plus I did not know you could update with CTE like that. 5 stars.

Thanks you for the kind words. You can also do inserts and deletes with a CTE the same way: I learned that in Microsoft SQL Server 2012 T-SQL Fundamentals. ;)

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001