mysql update column then select updated value

Question

I have a table like this

tbl_user

id user_id amount 

first i want to update a row based on id

$amount = 123; // dyanamic value $sql = "UPDATE tbl_user SET amount=amount-'$amount' WHERE id='$id' LIMIT 1 "; 

now i want to get updated value of amount column i have applied this sql

$sql = "SELECT amount FROM tbl_user  WHERE id='$id'  LIMIT 1 "; 

my question is can i combine both of above sql or any single query to achieve above task?

Answer 1

The best you could imitate is to use two lines of queries, probably using a variable like:

 UPDATE tbl_user SET      amount = @amount := amount-'$amount'  WHERE id='$id' LIMIT 1;   SELECT @amount; 

The best you could do then is to create a Stored Procedure like:

 DELIMITER //   CREATE PROCEDURE `return_amount` ()  BEGIN     UPDATE tbl_user SET      amount = @amount := amount-'$amount'     WHERE id='$id' LIMIT 1;      SELECT @amount;  END // 

And then call Stored Procedure in your PHP.

Note: PostgreSQL has this kind of option using RETURNING statement that would look like this:

 UPDATE tbl_user SET amount=amount-'$amount'   WHERE id='$id' LIMIT 1  RETURNING amount 

See here

Answer 2

A function can do this easily. It sounds like you want to limit how many times your code connects to the database. With a stored function or procedure, you are only making one connection. Yes, the stored function has two queries inside it (update then select), but these are executed on the server side without stopping to do round trips to the client.

http://sqlfiddle.com/#!2/0e6a09/1/0

Here's my skeleton of your table:

CREATE TABLE tbl_user (   id       VARCHAR(100) PRIMARY KEY,   user_id  VARCHAR(100),   amount   DECIMAL(17,4) );  INSERT INTO tbl_user VALUES ('1', 'John', '100.00'); 

And the proposed function:

CREATE FUNCTION incrementAmount   (p_id VARCHAR(100), p_amount DECIMAL(17,4)) RETURNS DECIMAL(17,4) BEGIN   UPDATE tbl_user SET amount = amount + p_amount WHERE id = p_id;   RETURN (SELECT amount FROM tbl_user WHERE id = p_id); END // 

Then you just run one query, a SELECT on the function you just created:

SELECT incrementAmount('1', 5.00) 

The query result is:

105