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

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

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.

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

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

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

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

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

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