Is there any difference between these two simple SQL Update statements?

UPDATE table1
SET col = 1
WHERE col2 = 'xyz'


UPDATE a
SET col = 1
FROM table1 a
WHERE col2 = 'xyz'

Which is preferred ?

One difference I could find out was that the second statement allows to use WITH NO LOCK near the 'FROM' statement. But when used near the update statement, it gives the same error message as that of the first statement

The NOLOCK and READUNCOMMITTED lock hints are not allowed for target tables of INSERT, UPDATE or DELETE statements.

Thanks So Much

2012-04-04 08:14
by Ananth

No difference in your specific example. The answers given so far mention the possibilities of joins. You should be warned that joins can be dangerous however - if the result of the join means that a row from the target table has been joined to multiple rows from other tables, and the update is based on values from the other tables, which row(s) will actually be used to perform the update is not deterministic - Damien_The_Unbeliever 2012-04-04 08:23

They are the same.

If the UPDATE statement only involves accessing a single table, the 1st is cleaner/simpler.

If you need to do e.g. a JOIN, then assigning an alias per your 2nd example is (IMO) cleaner, like:

UPDATE a
SET a.Col = 1
FROM TableA a
    JOIN TableB b ON a.Id = b.Id
WHERE b.Something = '123'

BTW, there is no point specifying a NOLOCK hint on the table being updated, so don't count that as a reason for one over the other.

2012-04-04 08:21
by AdaTheDev

+1 for (NOLOCK) on the UPDATED table is ignored on updates (will be honoured on joined tables). sp_lock shows no difference on SQL 2008 - StuartLC 2012-04-04 08:40

You can use the FROM syntax if you need or if it's confident to use aliases (like in your example) or JOINs like this one:

UPDATE a
SET col = 1
FROM table1 a
INNER JOIN table2 b ON b.id = a.bid
WHERE b.col = 'x'

2012-04-04 08:20
by Zeemee

One of the common scenarios for using method 2 is if you need to update based on a filter on joined tables, e.g.

UPDATE a
SET a.col = 1
FROM table1 a inner join table2 b on a.someFK = b.somePK
WHERE a.col2 = 'xyz' and b.col3 = 'abc'

2012-04-04 08:20
by StuartLC

Both are good, but it depends on what you need.

The first example is simpler, but doesn't allow you to use values from other tables.

Example 2 is very useful if you have to use values from other tables to be able to update your table, like demonstrated by Mulmoth.

2012-04-04 08:24
by Lex