Thread: Update multiple rows in a table with different values
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
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
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.
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
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
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