Обсуждение: 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