Clever Geek Handbook

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 preferable?

The only difference I could figure out was that the second statement allows WITH NO LOCK to be used next to the FROM clause. But when used next to the update operator, it gives the same error message as in the first statement

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

Many thanks

+3


source to share


4 answers


They are the same.

If the UPDATE statement only includes access to one table, the former is cleaner / simpler.

If you need to do, for example, a JOIN and then an alias assignment for your second example is a purge (IMO) like:



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

By the way, it doesn't make sense to have a NOLOCK hint on the table being updated, so don't take that as a reason for one over the other.

+3


source


You can use the FROM syntax if you need to, or if it is sure to use aliases (like in your example) or JOINs like this:



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


source


One of the common scenarios for using Method 2 is when you need to update based on a filter on joined tables, for example.

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'
+2


source


Both are fine, but it depends on what you want.

The first example is simpler, but does not allow using values ​​from other tables.

Example 2 is very helpful if you need to use values ​​from other tables in order to be able to update the table as shown by Mulmot.

+1


source






More articles:


All Articles