Re: Update multiple rows in a table with different values

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> shankha <shankhabanerjee(at)gmail(dot)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