Re: Update multiple rows in a table with different values

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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com