Re: Calculation in update query

Christopher A. Goodfellow wrote:
 >   I want to be able to update a value in a table to the existing value plus
 > or minus a value set in a variable without having to read in the current
 > value first.  Any help would be appreciated.
 >
 >   The desired action could be described like the following query:
 >
 >   UPDATE tablename SET columnname='currentvalue_in_column +-
 > value_in_variable' WHERE key='key_variable';

I would use SELECT FOR UPDATE, perform the calculation on
the client side, then UPDATE the column, and finally COMMIT
the transaction.  See the description of the "FOR UPDATE"
clause of the SELECT command (in PostgreSQL's online docs),
which ensures that the updated row is locked, so there is
no danger using SELECT + UPDATE.

HTH.

Best regards
   Oliver

--
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.

"anyone new to programming should be kept as far from C++ as
possible;  actually showing the stuff should be considered a
criminal offence" -- Jacek Generowicz