Get value from updated row

Question

I am trying to get a new rating from 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 get the value or row when updating?

Answer 1

In MySQL:

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

Answer 2

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

You have three options:

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

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

3) Use a database specific extension such as a PostgreSQL RETURNING clause

, 2) 3) .

Answer 3

, :

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;

(, , , ) , , ?

Answer 4

PostgreSQL RETURNING

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

Answer 5

, ( , , ).

:

my_package.updateAndReturnRating(refId, cursor record). 

, / , "" .