Re: Calculation in update query

From: Oliver Fromme <olli(at)lurza(dot)secnetix(dot)de>
To: cgoodfellow(at)tealuxe(dot)com
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Calculation in update query
Date: 2004-09-30 16:03:06
Message-ID: 200409301603.i8UG36Vt047253@lurza.secnetix.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice


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

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Oliver Fromme 2004-09-30 16:58:16 Re: Calculation in update query
Previous Message Christopher A. Goodfellow 2004-09-30 15:39:09 Calculation in update query