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 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 2

In MySQL:

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