Updating lots of database fields in a single row
William Blunn
bill+london.pm at blunn.org
Thu Jan 24 19:00:21 GMT 2013
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
More information about the london.pm
mailing list