get value from updated row

Question

I'm trying to get the new rating from an UPDATE statement in java

int userID = 99;

String sql = "UPDATE table SET rating=rating+1 WHERE user_REF="+userID;
statement.executeUpdate(sql);

I can just do another SELECT statement, but isn't there a better way to retrieve the value or row while updating?

Answer 1

In PostgreSQL there is RETURNING clause

See: http://www.postgresql.org/docs/8.3/interactive/sql-update.html

Answer 2

In MySQL:

$query1 = 'UPDATE `table` SET rating = (@rating:= rating) + 1 WHERE id = 1';
$query2 = 'select @rating';

Answer 3

Be cautious that most solutions are database dependent (Whether or not you want database independence in your application ofcourse matters).

Also one other solution you could try is to write a procedure and execute it as follows

my_package.updateAndReturnRating(refId, cursor record). 

Of course this may/may not make the solution itself complicated but worth an "evaluation" atleast.

Answer 4

In short, No, there is no way to do this with ANSI standard SQL.

You have three options:

1) Do it in two queries - the update, then the select

2) Create a stored procedure that will execute the update and then return the select

3) Use a DB-specific extension, such as the PostgreSQL RETURNING clause

Note that options 2) and 3) are database-specific.

Answer 5

thanks for the replies everybody, i ended up doing it like this:

int userID = 99;

String sql = "SELECT id, rating FROM table WHERE user_REF="+userID;
ResultSet rs = statement.executeQuery(sql);

rs.first();
float oldRating = rs.getFloat("rating");
float newRating = oldRating +1;

rs.updateFloat("rating", newRating);
rs.updateRow();

return newRating;

that way it (or at least seems so) does only one query to find the correct row, or am i wrong?