CODEWITHSUNDEEP
sqlpostgresql
Tim
Tim

Reputation: 1072

Update multiple rows using PostgreSQL

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

Upvotes: 0

Views: 277

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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;

Upvotes: 2

Belayer
Belayer

Reputation: 14861

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.

Upvotes: -1

Related Questions