What is the difference between UPDATE FROM WHERE and UPDATE FROM INNER JOIN?

Question

DECLARE @DATA TABLE
(
    Id int,
    Value int
)
INSERT INTO @DATA VALUES (1, 10)
INSERT INTO @DATA VALUES (2, 20)
INSERT INTO @DATA VALUES (3, 30)
INSERT INTO @DATA VALUES (4, 40)

DECLARE @TO_FILL TABLE
(
    Id int,
    Value int
)
INSERT INTO @TO_FILL VALUES (1, 100)
INSERT INTO @TO_FILL VALUES (3, 300)

DECLARE @METHOD int = 0

IF @METHOD = 0
BEGIN
    UPDATE @DATA
    SET Value = source.Value
    FROM @TO_FILL source
    WHERE [@DATA].Id = source.Id
END
ELSE
BEGIN
    UPDATE @DATA
    SET Value = source.Value
    FROM @DATA destination
    INNER JOIN @TO_FILL source ON destination.Id = source.Id
END

SELECT *
FROM @DATA

The idea is to update the table @DATA with the values from @TO_FILL, whenever the id matches. Whether @METHOD is set to 0 or not, this query will provide the same result.

I could understand the logic behind the block that uses the WHERE clause, which would be :

Update table @DATA
For each row, modify column Value
Use the column Value from table @TO_FILL, alias source
Do the modification whenever both columns Id match

But I have a hard time figuring the reasoning behind the second block, that uses the INNER JOIN clause. In my eyes, there are three 'temporary instances' of table : @DATA, @DATA alias destination, @TO_FILL alias source. destination and source are being joined to determine the set of rows that have to be modified (let's call it @DATA_TO_FILL), but I can't see how it is linked to the first table (@DATA). Since there are no WHERE or INNER JOIN clause between @DATA and @DATA_TO_FILL, how come it works ?

To answer the question, in the second case he is doing a join with the update instead of updating the table directly. It almost seems unnecessary but there could be a reason I am not seeing. It may be working because based on your dataset it does actually find a match between the two tables. — logixologist, May 14 '14 at 23:18
http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause -- While this is for `select` statements, I'm pretty sure it applies the same for `update` statements as well. — sgeddes, May 14 '14 at 23:19

score 3 · Answer 1 · answered May 14 '14 at 23:19

Both queries are essentially doing the same thing, difference is in the syntax.

1st Query

Its is using old join syntax where join condition is mentioned in where clause something like this...

SELECT *
FROM table1 , Table2
WHERE Table1.ID = Table2.ID

2nd Query

your second block uses newer ansi join syntax, key word JOIN is used and join condition is mentioned in ON clause, which is something like this.....

SELECT *
FROM table1 INNER JOIN Table2
ON Table1.ID = Table2.ID

The result set will be the same from both queries but its only the syntax difference, 2nd method is preferred syntax. stick to it.

Can you explain why it is old or new? That's a detail but I find this interesting. — A.L, May 15 '14 at 01:27

score 2 · Accepted Answer · answered May 14 '14 at 23:19

From the relevant TechNet page:

If the object being updated is the same as the object in the FROM clause and there is only one reference to the object in the FROM clause, an object alias may or may not be specified. If the object being updated appears more than one time in the FROM clause, one, and only one, reference to the object must not specify a table alias. All other references to the object in the FROM clause must include an object alias.

In other words, in your example, the fact that you assign an alias to @DATA in the FROM clause doesn't prevent SQL Server from recognizing that it's the same table you're updating, since there's no ambiguity. However, if your FROM clause involved joining @DATA to itself, then you would have to specify which is the table to be updated by omitting an alias from one instance.

score -1 · Answer 3 · answered May 14 '14 at 23:52

Combining the two answers above:

Firstly there is no difference between using WHERE to join tables or INNER JOIN, except that WHERE is older syntax and INNER JOIN is newer syntax which you should make a habit of using.

Secondly don't be confused by the FROM @DATA destination in the second SQL. It's sole purpose is to give @DATA an alias of destination. That's it.

Whoever wrote this has for some reason used totally different syntaxes to do exactly the same thing.

Personally I prefer the second way of doing it because:

-It uses INNER JOIN instead of WHERE

-It assignes the target table an alias which can make queries easier to understand.

I downvoted this answer because it's repeating the other answers without adding much information. — A.L, May 15 '14 at 01:29
I upvoted both of the other answers but then realised that neither of them tell the full story. — Nick.Mc, May 15 '14 at 22:55

What is the difference between UPDATE FROM WHERE and UPDATE FROM INNER JOIN?

3 Answers3