Update different columns on multiple rows in a single query

Question

Let's say we have the following table:

id | col_a | col_b | col_c
1  |   abc |  null |  qwqw
2  |  null |  null |  null
3  |  null |  ijij |  cba

And we want to do the following updates:

• row 1: SET col_a = cba
• row 2: SET col_b = uiui, col_c = zxzx
• row 3: SET col_b = null

First, is it possible to do it in one query?

If not, what's the best alternative?

• One query per row, like UPDATE table SET ... WHERE id=1;
• Fetch the rows, replace the old values with the new values, and do a massive INSERT INTO table VALUES ... ON CONFLICT (id) DO UPDATE SET col_a = EXCLUDED.col_a, col_b = EXCLUDED.col_b, col_c = EXCLUDED.col_c;
• Something else?

Answer 1

The simplest method is three updates:

update t
    set col_a = cba
    where id = 1;

update t
    set col_b = uiui, col_c = zxzx
    where id = 2;

update t
    set col_b = null
    where id = 3;

You can wrap these in a transaction so they take effect at the same time. Assuming you have an index on id, this should have good performance.

You can put these into a single statement using conditional logic:

update t
    set col_a = (case when id = 1 then cba else col_a end),
        col_b = (case when id = 2 then uiui 
                      when id = 3 then null
                      else col_b
                 end),
        col_c = (case when id = 2 then zxzx else col_c end)
    where id in (1, 2, 3);

I think three separate statements is clearer and less prone to error.