Updating lots of database fields in a single row

[William Blunn]

On 22/01/2013 22:57, Andrew Beverley wrote:
> Basically, I'd like to update lots of fields in a single database row.

 From the subject line I thought you were going to ask how to update 
lots of fields in lots of rows in a single query...

Considering a table "table" with primary key columns "pkc1" and "pkc2", 
and non-key columns "f1" and "f2", in PostgreSQL you can update multiple 
rows with different values in a single query using:

UPDATE table
     SET f1 = data.f1, f2 = data.f2
     FROM (
         VALUES
             ($r1pkc1, $r1pkc2, $r1f1, $r1f2),
             ($r2pkc1, $r2pkc2, $r2f1, $r2f2),
             ($r3pkc1, $r3pkc2, $r3f1, $r3f2),
             ...
             ($rnpkc1, $rnpkc2, $rnf1, $rnf2)
     ) data (pkc1, pkc2, f1, f2)
     WHERE table.pkc1 = data.pkc1 AND table.pkc2 = data.pkc2;


Regards,

Bill