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)?

Tim · Accepted Answer · 2020-08-19 18:58:46Z

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, 2020 at 18:58

Tim

1,08213 silver badges15 bronze badges

answered Aug 6, 2020 at 19:44

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tim Over a year ago

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.

Gordon Linoff Over a year ago

@TimMoses . . . There is no reason to suspect that the columns would have different types; your question doesn't even provide sample data.

Tim Over a year ago

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.

Belayer · Accepted Answer · 2020-08-06 23:22:21Z

-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 6, 2020 at 23:22

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

2 Comments

Tim Over a year ago

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.

Belayer Over a year ago

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.

Collectives™ on Stack Overflow

Update multiple rows using PostgreSQL

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related