| 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: | Whole Thread | Raw Message | 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
| 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 |