Search Postgresql Archives

Re: Update multiple rows in a table with different values

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxx


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux