CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
Luiz
Luiz

Reputation: 23

Which of these SQL UPDATE statements would be faster?

Which would be faster?

Method A:

UPDATE table1
SET table1.column1 = table2.column1 
FROM table2
WHERE table1.column2 = table2.column2

Method B:

UPDATE table1
SET table1.column1 = table2.column1 
FROM table1
JOIN table2 on table1.column2 = table2.column2

Will they generate the same execution plan?

Is there any case where I should avoid one of them?

Some tests I did took them almost the same time to execute, but always good to hear a second opnion.

Upvotes: 2

Views: 198

Answers (1)

Yuck
Yuck

Reputation: 50835

They are equivalent. You can verify this by checking the execution plan yourself. The second option:

UPDATE table1
SET table1.column1 = table2.column1
FROM table1 JOIN
     table2 on table1.column2 = table2.column2

Is currently the preferred method for writing queries, as it's more clear why the criteria is being specified.

Upvotes: 8

Related Questions