SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Update question on order of tables in join


Update question on order of tables in join

Author
Message
tshad
tshad
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 454
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
Alan.B
Alan.B
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5302 Visits: 7736
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.

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"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
davoscollective
davoscollective
SSC Eights!
SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)

Group: General Forum Members
Points: 943 Visits: 1004
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.
tshad
tshad
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 454
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26626 Visits: 17557
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
tshad
tshad
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 454
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26626 Visits: 17557
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
tshad
tshad
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 454
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26626 Visits: 17557
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Alan.B
Alan.B
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5302 Visits: 7736
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. Wink

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search