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@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