Update multiple rows in a table with different values

From: shankha <shankhabanerjee(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Update multiple rows in a table with different values
Date: 2016-07-01 14:26:27
Message-ID: CAO_L6qERUvQSbD0uTY0t8ASsdAcpZrc6MB01cENnma6pfFRawQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Greetings,
I have the following schema:

CREATE TABLE "s"."t1"
(
"c1" BigSerial PRIMARY KEY,
"c2" BigInt NOT NULL,
"c3" BigInt
)
WITH (OIDS=FALSE);

INSERT INTO s.t1 (c2, c3) VALUES (10, 100);
INSERT INTO s.t1 (c2, c3) VALUES (20, 200);
INSERT INTO s.t1 (c2, c3) VALUES (30, 300);
INSERT INTO s.t1 (c2, c3) VALUES (40, 400);

PREPARE updateplan (BigInt, BigInt) AS
update s.t1
SET c3 = $2
WHERE c2 = $1;

EXECUTE updateplan (20, 250);
***
PREPARE updatearrayplan(BigInt[], BigInt[]) AS
for i in size($1)
DO
update s.t1
SET c3 = $2[$i]
WHERE c2 = $1[$i]
END FOR

EXECUTE updatearrayplan({20, 30}, {275, 375})
***
/* 20, 200 -> 20, 275 */
/* 30, 300 -> 30, 375 */
***

After execution of updatearrayplan I am expecting the rows to have
these values 20 -> 275 , 30 -> 375

Is there a way to update multiple rows with different column values
passed in as array. Also is there a guarantee that the order of the
arrays will be maintained.

Thanks

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2016-07-01 14:48:14 Re: Update multiple rows in a table with different values
Previous Message Tom Lane 2016-07-01 14:03:39 Re: table name size