Postgres: How to update multiple records in a table with a single command

Question

I have a table and I want to update multiple fields to complete the table so all fields are filled, how do I do this in a Postgres script? As all I can find is how to update one record at a time, or how to loop through and change everything to the same value.

With 100’s of records to update this will take ages

oid	name	nickname	dob
0	Chris	Cross	01Jan1985
1	Richard		02Feb1896
2	Michael	Mikey
3	Jonathan

Currently I can update one field with the following:

    UPDATE mytable SET nickname = 'Rick' Where oid = 1;

But how do I do this to change all these?

• nickname = ‘Rick’ where oid = 1
• nickname = ‘Jono’ where oid = 3
• dob = ‘03Mar1987’ where oid = 2
• dob = ‘04Apr1988’ where oid = 3

Thanks in Advance Daz

Answer 1

You can use UPDATE ... FROM and join with the new values:

UPDATE mytable
SET nickname = coalesce(x.nickname, mytable.nickname)
    dob = coalesce(x.dob, mytable.dob)
FROM (SELECT id,
             max(nickname) AS nickname,
             max(dob) AS dob
      FROM (VALUES (1, 'Rick', NULL),
                   (3, 'Jono', NULL),
                   (2, NULL, '1987-03-03'),
                   (3, NULL, '1988-04-04')) AS y(id, nickname, dob)
      GROUP BY id
     ) AS x
WHERE mytable.id = x.id;

Since an UPDATE can change each row only once, you have to aggregate by id.