Sql-server – How to update multiple columns of multiple rows in one SQL statement

sql serverupdate

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?

Best Answer

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
Related Question