Is it possible to write an update statement that returns the updated value?

Something like:

update mytable set field1=field1+1 where keyfield=123 returns field1

I don't think so. You could investigate a script though.

Roy Lambert

<< Is it possible to write an update statement that returns the updated value? >>

No. I could look into adding this, but it would require:

1) That the UPDATE statement only update a single row.
2) The UPDATE statement is parameterized

Tim Young
Elevate Software
www.elevatesoft.com

<< Is it possible to write an update statement that returns the updated value? >>

> No. I could look into adding this, but it would require:
> 1) That the UPDATE statement only update a single row.
> 2) The UPDATE statement is parameterized

Those are acceptable restrictions

> I don't think so. You could investigate a script though.

Script won't work.  The point is to do the update and read of value atomically so as to get the new value as updated before someone else changes it.

You would have to use transactions to make that work in multiple statements which introduces extra locking issues. If you can do it in a single statement, it solves a lot of problems.

> << Is it possible to write an update statement that returns the updated value? >>

A simple procedure or script with an additional output param will do it:

SCRIPT
BEGIN
EXECUTE IMMEDIATE 'CREATE PROCEDURE "GetNewValue" (IN "FID" INTEGER, IN
"FOffset" INTEGER, OUT "FResult" INTEGER)
BEGIN

   Execute Immediate ''UPDATE MyTable SET Counter = Counter + ? WHERE
ID = ?'' USING FOffset, FID;
   Execute Immediate ''SELECT Counter INTO ? FROM MyTable WHERE ID =
?'' USING FResult, FID;

END
VERSION 1.00';
END

Uli

> A simple procedure or script with an additional output param will do it:

Not atomically.

<< Those are acceptable restrictions >>

I'll look into sneaking it into the next build.

Tim Young
Elevate Software
www.elevatesoft.com