From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | shankha <shankhabanerjee(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Update multiple rows in a table with different values |
Date: | 2016-07-01 14:50:43 |
Message-ID: | CAKFQuwYrcMA=M=NeXz6TeMWu0-Ek6-aM0vxhh9_4OJFszzntow@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Fri, Jul 1, 2016 at 10:26 AM, shankha <shankhabanerjee(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | shankha | 2016-07-01 14:51:01 | Re: Update multiple rows in a table with different values |
Previous Message | Adrian Klaver | 2016-07-01 14:48:14 | Re: Update multiple rows in a table with different values |