What query is faster: one update with multiple sets or multiple updates with one set?

Question

Suppose you have TestTable with columns: field1, field2, ... fieldn.

What query executes faster:

UPDATE TestTable set field1 = n1;

UPDATE TestTable set field2 = n2;

...

UPDATE TestTable set fieldn = nn;

or

UPDATE TestTable set 

field1 = n1,

field2 = n2,

....

fieldn = nn;

Answer 1

Good question. Try thinking of it this way.

Every set operation takes negligible time
for every UPDATE = n checks

UPDATE n1 = n
UPDATE n2 = n
UPDATE n3 = n

So essentially, for n number of updates, you are checking n rows n times, so you are looking at n^2 total checks. However, if you utilize a SINGLE UPDATE function, you are only checking the n rows ONE time.

Therefore, the second option is significantly better.