Re: Update multiple rows in a table with different values

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.klaver@aklaver.com