Update multiple rows using PostgreSQL

Question

I'm needing to update multiple rows in the same sql transaction using PostgreSQL. From the post below: Update multiple rows in same query using PostgreSQL I see the following code:

UPDATE test AS t SET
column_a = c.column_a,
column_c = c.column_c
FROM (values
  (123, 1, '---'),
  (345, 2, '+++')  
) AS c(column_b, column_a, column_c) 
WHERE c.column_b = t.column_b;

BUT, this is only if you are updating all columns for each set of values, in which I'm not. Does anyone have a solution to this to allow multiple updates use just SQL alone (not plpgsql)?

score 2 · Accepted Answer · edited Aug 19 '20 at 18:58

2

Assuming you are not updating to NULL values, you could use:

UPDATE test t
    SET column_a = COALESCE(c.column_a, t.column_a),
        column_c = COALESCE(c.column_c, t.column_c
FROM (values ('123', 1, NULL),
             ('345', NULL, '+++')  
     ) c(column_b, column_a, column_c) 
WHERE c.column_b = t.column_b;

EDIT:

If the values can be NULL, then you need additional columns to specify if the value should be used:

UPDATE test t
    SET column_a = (CASE WHEN c.use_a THEN c.column_a::numeric ELSE t.column_a END),
        column_c = (CASE WHEN c.use_b THEN c.column_c::varchar ELSE t.column_c END)
FROM (values (123, 1, NULL, true, false),
             (345, NULL, '+++', false true)  
     ) c(column_b, column_a, column_c, use_a, use_c) 
WHERE c.column_b::int4 = t.column_b;

edited Aug 19 '20 at 18:58

Tim

1,105
13
14

answered Aug 06 '20 at 19:44

Gordon Linoff

1,242,037
58
646
786

Your edit almost works, but in the CASE statements, you have to cast the values to the same type as the original column value to get a consistent return value. I tested it using postgreSQL 11.1 and an error is thrown stating the above. I'll see if I can edit your answer and then I'll mark it as the actual answer to this question. – Tim Aug 19 '20 at 17:00
@TimMoses . . . There is no reason to suspect that the columns would have different types; your question doesn't even provide sample data. – Gordon Linoff Aug 19 '20 at 18:58
My question does provide sample data "('123', 1, '---'), ('345', 2, '+++') ". Anyways, for the above solution to "work" it has to be casted to certain types. Yeah, I didn't provide types or further info in the question but I'm just saying it will not work without them. – Tim Aug 19 '20 at 19:01

score -1 · Answer 2 · answered Aug 06 '20 at 23:22

-1

Your contention "this is only if you are updating all columns for each set of values" is incorrect. The update will effect only those columns mentioned in the SET clause. Since you accept NULL as new values then just a straight set for the specific columns:

update test t
    set column_a = c.column_a
      , column_c = c.column_c 
 from (values ('123', 1, NULL, 'Ignored Column')
            , ('345', NULL, '+++','Ignored Column')  
      ) c(column_b, column_a, column_c,column_d) 
where c.column_b = t.column_b ;

See fiddle, particularly the additional column_d.

answered Aug 06 '20 at 23:22

Belayer

13,578
2
11
22

When I said, "set of values" I was referring to those mentioned in the set clause. Also, the "ignored" column may or may not be given. – Tim Aug 07 '20 at 00:57
Then what do you mean by "which I not". Under what condition would you not update a column which that's in the set of values. This needs to be clearly defined. – Belayer Aug 07 '20 at 03:56

Update multiple rows using PostgreSQL

2 Answers2