May 16, 2014 at 6:15 pm
Quick question.
Normally an update with a join would be something like:
Update table1
SET name = t2.firstName
FROM table1 t1
JOIN table2 t2
ON table1.someid = table2.someid
This is how I normally see it and do it.
You have the same table after the UPDATE as after the FROM.
But does it matter what order the table is in the join?
Would this also work?
Update table1
SET name = t2.firstName
FROM table2 t2
JOIN table1 t1
ON table1.someid = table2.someid
Now the UPDATE table is not after the FROM but after a JOIN?
This came up because of a statement I did where the UPDATE table is after the last join:
UPDATE Users
SET bIsActive = 0
FROM vEmpUser veu
JOIN Employee e
ON e.EmployeeID = veu.EmployeeID
JOIN veStaging ves
ON ves.EmployeeId = e.EmployeeID
JOIN Users pu
ON pu.User_id = veu.UserID
This seems to work but I want to make sure I am not missing something.
Does SQL Server look to see if there is a table in the FROM/JOIN clause that matches the table that follows the UPDATE use that record to update?
Thanks,
Tom
May 18, 2014 at 5:41 pm
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:
http://tsql.solidq.com/books/insidetsql2008/Logical%20Query%20Processing%20Poster.pdf
I personally prefer to use a CTE for this type of task.
Taking this sample data:
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);
INSERT #table1 VALUES (1,'old val'),(2,'old val'),(3,'old val');
INSERT #table2 VALUES (2),(3);
Instead of doing this:
UPDATE #table1
SET t_txt = 'New Val'
FROM #table1 t1
JOIN #table2 t2 ON t1.t_id = t2.t_id;
you could use a CTE like this:
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'
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.
-- Itzik Ben-Gan 2001
May 19, 2014 at 8:08 am
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:
http://tsql.solidq.com/books/insidetsql2008/Logical%20Query%20Processing%20Poster.pdf
I personally prefer to use a CTE for this type of task.
Taking this sample data:
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);
INSERT #table1 VALUES (1,'old val'),(2,'old val'),(3,'old val');
INSERT #table2 VALUES (2),(3);
Instead of doing this:
UPDATE #table1
SET t_txt = 'New Val'
FROM #table1 t1
JOIN #table2 t2 ON t1.t_id = t2.t_id;
you could use a CTE like this:
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'
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.
May 19, 2014 at 11:05 am
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
May 19, 2014 at 12:13 pm
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?
It updates #table1 because that is the column in your cte. The cte doesn't know anything about the column with the same name in #table2.
If you want to update it in #table2 you would either change the cte to select that column OR alias the column from #table2 in your cte and update the aliased name.
_______________________________________________________________
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/
May 19, 2014 at 12:28 pm
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
May 19, 2014 at 12:51 pm
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;
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, t2.t_txt --This is changed to t2
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
_______________________________________________________________
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/
May 19, 2014 at 2:07 pm
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
May 19, 2014 at 2:29 pm
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/
May 20, 2014 at 8:42 am
davoscollective (5/19/2014)
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:
http://tsql.solidq.com/books/insidetsql2008/Logical%20Query%20Processing%20Poster.pdf
I personally prefer to use a CTE for this type of task.
Taking this sample data:
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);
INSERT #table1 VALUES (1,'old val'),(2,'old val'),(3,'old val');
INSERT #table2 VALUES (2),(3);
Instead of doing this:
UPDATE #table1
SET t_txt = 'New Val'
FROM #table1 t1
JOIN #table2 t2 ON t1.t_id = t2.t_id;
you could use a CTE like this:
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'
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. 😉
-- Itzik Ben-Gan 2001
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply