How to update multiple columns of multiple rows in one SQL statement

Question

In my SQL Server database I want to update columns of multiple rows. I can do it for one row only:

UPDATE theTable
SET theColumn = case
WHEN id = 1 then 'a' 
WHEN id = 2 then 'b'
WHEN id = 3 then 'c'
WHERE id in (1, 2, 3)

How can I update more columns (theColumn2, theColumn3...) in the same query?

Answer 1

You could supply the new values as a table (with the help of the VALUES row constructor), so that you could join it with the target table and use the join in the UPDATE statement, like this:

UPDATE
 tgt
SET
 Column1 = src.Column1,
 Column2 = src.Column2,
 Column3 = src.Column3,
 ...
FROM
 dbo.TargetTable AS tgt
 INNER JOIN
 (
 VALUES
 (1, 'a', 'k', 'x', ...),
 (2, 'b', 'l', 'y', ...),
 (3, 'c', 'm', 'z', ...)
 ) AS src (ID, Column1, Column2, Column3, ...)
 ON tgt.ID = src.ID
;

Answer 2

An example of how this can be done (see SQLFiddle here):

(p.s. I used a CTE (aka the WITH clause) and PostgreSQL (I don't use MS SQL Server) but the principles are very much the same - except for the SERIAL datatype - use MS's auto-incrementing type!).

Create and populate a source table (named one):

CREATE TABLE one
(
 record_id SERIAL,
 one_first_var INTEGER,
 one_second_var INTEGER,
 one_third_var INTEGER
);
INSERT INTO one (one_first_var, one_second_var, one_third_var) VALUES (1, 1, 1);
INSERT INTO one (one_first_var, one_second_var, one_third_var) VALUES (2, 2, 2);
INSERT INTO one (one_first_var, one_second_var, one_third_var) VALUES (3, 3, 3);

And also a target table (two):

CREATE TABLE two
(
 record_id SERIAL,
 two_first_var INTEGER,
 two_second_var INTEGER,
 two_third_var INTEGER
);
INSERT INTO two (two_first_var, two_second_var, two_third_var) VALUES (21, 21, 21);
INSERT INTO two (two_first_var, two_second_var, two_third_var) VALUES (22, 22, 22);
INSERT INTO two (two_first_var, two_second_var, two_third_var) VALUES (23, 23, 23);

(double check your values in table two):

SELECT * FROM two;

And then run your update (multiple columns at a time):

WITH my_values AS
(
 SELECT 
 one_first_var, 
 one_second_var, 
 one_third_var
 FROM one
 WHERE one_first_var = 2
)
UPDATE two 
SET 
 two_first_var = my_values.one_first_var,
 two_second_var = my_values.one_second_var,
 two_third_var = my_values.one_third_var
FROM
 my_values
WHERE
 two_second_var = 22;

And then re-run your

SELECT * FROM two;

Again, see the SQLFiddle!

You can also use a JOIN to update the target record(s). I would encourage you to experiment with these techniques - very useful!

Your first result for two (i.e. inserted values) will look like this:

record_id two_first_var two_second_var two_third_var
 1 21 21 21
 2 22 22 22
 3 23 23 23

and your second (updated) result will be:

record_id two_first_var two_second_var two_third_var
 2 2 2 2
 1 21 21 21
 3 23 23 23