Thread: Calculation in update query
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'; Thank You, Christopher A. Goodfellow Director of Information Technology Tealuxe, Inc. Phone: 508 520 7887 ex:22 Fax: 508 528 8999 www.tealuxe.com tea for all
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
Christopher A. Goodfellow wrote: > So I do need to read (select) the value first, perform the calculation, and > then update the value? I just wanted to be able to set the value based on > the current value in one step. There are 10's of thousands of calculations > that will be performed in this loop. Three steps (Select, Calculate, > Update) are just longer than one. Thanks. Ah, sorry, I misunderstood you. I thought you were concerned about the possibility of concurrent updates, i.e. the necessity of locking. That's why my brain immediately went into the SELECT FOR UPDATE direction. :-) Well, yes, of course you can update a column based on the old value of that column. The UPDATE command accepts arbitrary expressions for the new value which may reference the old one. There's even an example which does exactly that (on the PostgreSQL's doc page on the UPDATE command): http://www.postgresql.org/docs/7.4/static/sql-update.html 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. "The ITU has offered the IETF formal alignment with its corresponding technology, Penguins, but that won't fly." -- RFC 2549