From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | shankha <shankhabanerjee(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Update multiple rows in a table with different values |
Date: | 2016-07-01 14:48:14 |
Message-ID: | 5a977d3a-22f9-101b-87da-4e4ee1ad69e7@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 07/01/2016 07:26 AM, shankha wrote:
> Greetings,
> I have the following schema:
>
> CREATE TABLE "s"."t1"
> (
> "c1" BigSerial PRIMARY KEY,
> "c2" BigInt NOT NULL,
> "c3" BigInt
> )
> WITH (OIDS=FALSE);
Unless you have a very old version of Postgres, OIDS=FALSE is the default.
>
> 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
I am not familiar with the above syntax, are you using a Postgres
version different from the community version?
>
> 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
>
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2016-07-01 14:50:43 | Re: Update multiple rows in a table with different values |
Previous Message | shankha | 2016-07-01 14:26:27 | Update multiple rows in a table with different values |