SQL Query: Update From

Question

Is this correct SQL:

UPDATE T1alias
SET T1alias.Row2 = T2alias.Row2
FROM 
(
   T1 AS T1alias
   INNER JOIN 
   T2 AS T2alias
   ON T1alias.Row1 = T2alias.Row1
)

This query seems to return the right results, but I dont understand why. I mean the FROM clause refers to an complete different Dataset as to the table T1 which has to be updated. F.e.:

 T1                                 T2
----------------------             ----------------------
| Row1 | Row2 | Row3 |             | Row1 | Row2 | Row3 |  
----------------------             ----------------------
| 1    | 2    | 3    |             | 1    | 7    | 8    |  
---------------------              ----------------------
| 4    | 5    | 6    |             | 9    | 10   | 11   |  
----------------------              ----------------------

 T1 INNER JOIN T2 ON T1alias.Row1 = T2alias.Row1
-------------------------------------------------------------
| T1.Row1 | T1.Row2 | T1.Row3 | T2.Row1 | T2.Row2 | T2.Row3 |  
-------------------------------------------------------------
| 1       | 2       | 3       |  1      | 7       | 8       |  
-------------------------------------------------------------

So how can I UPDATE T1 from the joined Table?

In my opinion these are complete different datasets. I would understand the sql query if it would look like:

UPDATE T1alias
SET T1alias.Row2 = T2alias.Row2
FROM 
(
   T1 AS T1alias
   INNER JOIN 
   T2 AS T2alias
   ON T1alias.Row1 = T2alias.Row1
) AS T1T2JoinedAlias
WHERE T1T2JoinedAlias.Row1 = T1alias.Row1

Could someone explain these to me, please. (I m working on Microsoft SQL Server 2008 R2)

See [this question](http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server) — Fabian, Nov 25 '13 at 10:20
Your last query is wrong. You cannot have alias for a table join in an update query. — Ganesh Jadhav, Nov 25 '13 at 10:30
@Fabian if you mean the comment "This works by using UPDATE to iterate over the INNER JOIN. As such the ON functions as your WHERE clause and the INNER JOIN skips records that are not found in the JOINed table." But i would be glad if someone could explain it in more detail. — user1911091, Nov 25 '13 at 11:49
@user1911091 The linked question and answers (in particular the first answer) discuss several ways to update a single table based on a join of that table to another table. I understood that this is what you are looking for? — Fabian, Nov 25 '13 at 12:39
I am more interessted in the why and how does it work. I know the syntax (as you can see from my question). But i dont understand why this query is giving the right results. Because the joined table from the where clause is a total different dataset from T1, which is updated. How does the SQL Server 2008 handles the connection. How does it know which rows of T1 to update. Because i did not define a rule. — user1911091, Nov 25 '13 at 13:42
Oh, I see. I missed that point. The UPDATE FROM extension of SQL Server requires that the table that is being updated appears (uniquely identified) in the FROM clause (which is a join of multiple tables/sub-queries). Thus, the dataset returned by the FROM part always contains the tuples of the table that is updated. The FROM clause hence implicitly acts as a filter, too (when not all rows in the updated table are contained in the join). AFAIK, if a row of the table that is to be updated is matched *multiple* times in the join, it is nevertheless updated only once. — Fabian, Nov 25 '13 at 18:08

score 1 · Accepted Answer · answered Nov 25 '13 at 18:00

If you look at the execution plan of your SQL statement you will understand what is going on:

Execution Plan of Update Query

As you can see (in my case) the Query Optimiser does a scan of both tables specified in the FROM clause and retrieves rows that fulfil the inner join.

These rows are then passed along the chain to the Table Update physical operator which, as you can see, is told to perform an update on T1 (you tell it to do this by saying "Update T1Alias" in your query above, you also tell it which field(s) to update by your SET command)

The query analyser tends to choose the best execution plan for your query after the algebrizer has compiled it into binary, so whether you get the same execution plan as me or not will depend on a number of factors including whether you have indexes on the tables.

Hope this helps.

SQL Query: Update From

1 Answers1