Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update question on order of tables in join Expand / Collapse
Author
Message
Posted Friday, May 16, 2014 6:15 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 9:35 PM
Points: 142, Visits: 344
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
Post #1571957
Posted Sunday, May 18, 2014 5:41 PM This worked for the OP Answer marked as solution


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 4:49 AM
Points: 556, Visits: 2,581
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



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1572116
Posted Monday, May 19, 2014 8:08 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:03 AM
Points: 450, Visits: 842
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.
Post #1572296
Posted Monday, May 19, 2014 11:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 9:35 PM
Points: 142, Visits: 344
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
Post #1572391
Posted Monday, May 19, 2014 12:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:22 AM
Points: 13,106, Visits: 11,935
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 Moden's 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)
Post #1572413
Posted Monday, May 19, 2014 12:28 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 9:35 PM
Points: 142, Visits: 344
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
Post #1572420
Posted Monday, May 19, 2014 12:51 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:22 AM
Points: 13,106, Visits: 11,935
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 Moden's 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)
Post #1572432
Posted Monday, May 19, 2014 2:07 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 9:35 PM
Points: 142, Visits: 344
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
Post #1572447
Posted Monday, May 19, 2014 2:29 PM This worked for the OP Answer marked as solution


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:22 AM
Points: 13,106, Visits: 11,935
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 Moden's 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)
Post #1572451
Posted Tuesday, May 20, 2014 8:42 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 4:49 AM
Points: 556, Visits: 2,581
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. ;)


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1572714
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse