CODEWITHSUNDEEP
javasqlmysqljdbc
iddqd
iddqd

Reputation: 700

get value from updated row

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?

Upvotes: 5

Views: 2880

Answers (5)

iddqd
iddqd

Reputation: 700

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?

Upvotes: 1

Timothy
Timothy

Reputation: 2477

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.

Upvotes: 2

Kannan Ekanath
Kannan Ekanath

Reputation: 17631

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.

Upvotes: 0

Mike Sherov
Mike Sherov

Reputation: 13427

In MySQL:

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

Upvotes: 1

skyman
skyman

Reputation: 2472

In PostgreSQL there is RETURNING clause

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

Upvotes: 0

Related Questions