Re: Postgres and multiple updates in one statement

Hello again,

Le 27/07/09 16:48, Jamie Lawrence-Jenner a écrit :
> Hi There
>
> Our update statements are as follows
>
> Update table set col1=x,col2=y where pkid=1;
> Update table set col1=x,col2=y where pkid=2;
> Update table set col1=x,col2=y where pkid=3;
>
> Very simple and straight forward. Sometimes there could be as many as 50
> update statements to process.
>
> Many thanks
>
> Jamie
> [...]
> Improvements on the merging UPDATE statements may thence be advised if
> some pieces of original statements could be given--without compromising
> confidential data.
>

Thanks for the examples.

Comparison between "explain analyze" outputs from the two ways (multiple
statements vs. one statement) should help choosing the faster.

For the one-statement schema, the rewritten query could be:

UPDATE yTable SET col1=x, col2=y WHERE pkID IN (1, 2, 3);

Lists of pkID would rather be expressed in terms of enumeration when in
WHERE clause. In a more general situation, I would recommand to
determine pkIDs list before building UPDATE statement(s) if possible.
This hint would surely save runtime.

With regards.
--
nha / Lyon / France.