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 -----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of nha Sent: 27 July 2009 14:35 To: Jamie Lawrence-Jenner Cc: PgSQL-general Subject: Re: Postgres and multiple updates in one statement Hello, Le 27/07/09 15:07, Raymond O'Donnell a écrit : > On 27/07/2009 09:10, Jamie Lawrence-Jenner wrote: > >> Apart from saving on the overhead of having to open up 5 separate >> connections, what are the benefits to passing in multiple updates in one >> statement? > > If you do them all within one transaction - > > begin; > update.... > update... > ... > commit; > > - then you save on the overhead associated with beginning and committing > a transaction for each update. > Next to the transaction way suggested by Raymond O'Donnell, I would add that performance would depend on FROM and WHERE clauses specified in the original UPDATE statements. In the case of multiple UPDATE statements following a quite "similar" schema (ie. similar FROM (optional) and WHERE clauses), it might be clearer (for the source code) and faster (for the database engine) to merge them in a single UPDATE statement. Otherwise (ie. UPDATE statements with not so much FROM and/or WHERE clauses in common), transaction 1-block statement as suggested by Raymond O'Donnell would certainly be the more appropriate. Improvements on the merging UPDATE statements may thence be advised if some pieces of original statements could be given--without compromising confidential data. Regards. -- nha / Lyon / France. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
