Обсуждение: Update multiple rows in a table with different values

Поиск
Список
Период
Сортировка

Update multiple rows in a table with different values

От
shankha
Дата:
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

Re: Update multiple rows in a table with different values

От
Adrian Klaver
Дата:
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

Re: Update multiple rows in a table with different values

От
"David G. Johnston"
Дата:
On Fri, Jul 1, 2016 at 10:26 AM, shankha <shankhabanerjee@gmail.com> wrote:

    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})

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


Have you looked at CREATE FUNCTION​?

I'd suggest the plpgsql language.
 
Is there a way to update multiple rows with different column values
passed in as array.

​No.  All rows identified by a single where clause are updated using the same expression.  Though I suppose you could try something like:

c3 = CASE WHEN c2= 20 THEN 275 WHEN c2= 30 THEN 375​ END
WHERE c2IN (20, 30)
 
Also is there a guarantee that the order of the
arrays will be maintained.

That question is too broad.  Direct iteration of an array will be done in order.  Whether, post-iteration, the resultant records remain in order is not promised.

David J.

Re: Update multiple rows in a table with different values

От
shankha
Дата:
Hi Adrian,
I am using Postgres version 9.3.

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

In this prepared statement I am just trying to explain the algorithm.
I do not know the exact syntax.

Sorry for the confusion.

Thanks
Shankha Banerjee


On Fri, Jul 1, 2016 at 10:48 AM, Adrian Klaver
<adrian.klaver@aklaver.com> wrote:
> 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

Re: Update multiple rows in a table with different values

От
Tom Lane
Дата:
shankha <shankhabanerjee@gmail.com> writes:
> PREPARE updatearrayplan(BigInt[], BigInt[]) AS
>         for i in size($1)
>         DO
>             update s.t1
>             SET c3 = $2[$i]
>             WHERE c2 = $1[$i]
>         END FOR

> In this prepared statement I am just trying to explain the algorithm.
> I do not know the exact syntax.

You would need to write a plpgsql function in order to have a loop like
that; there's no loops in bare SQL.

            regards, tom lane

Re: Update multiple rows in a table with different values

От
shankha
Дата:
Hi Tom,
Thanks for your suggestion.

I got it working:

CREATE OR REPLACE FUNCTION s.updatefunc1(BigInt[], BigInt[])
RETURNS void as $$
BEGIN
    FOR i IN array_lower($1, 1) .. array_upper($1, 1)
    LOOP
        update s.t1
        SET c3 = $2[i]
        WHERE c2 = $1[i];
    END LOOP;
END;
$$
LANGUAGE plpgsql;


Is there a better way to do it using : unnest.

Thanks
Shankha Banerjee


On Fri, Jul 1, 2016 at 10:59 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> shankha <shankhabanerjee@gmail.com> writes:
>> PREPARE updatearrayplan(BigInt[], BigInt[]) AS
>>         for i in size($1)
>>         DO
>>             update s.t1
>>             SET c3 = $2[$i]
>>             WHERE c2 = $1[$i]
>>         END FOR
>
>> In this prepared statement I am just trying to explain the algorithm.
>> I do not know the exact syntax.
>
> You would need to write a plpgsql function in order to have a loop like
> that; there's no loops in bare SQL.
>
>                         regards, tom lane