Mysql Update Column Then Select Updated Value

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' W

Solution 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 //CREATEPROCEDURE `return_amount` ()
 BEGINUPDATE 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

Solution 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:

CREATETABLE tbl_user (
  id       VARCHAR(100) PRIMARY KEY,
  user_id  VARCHAR(100),
  amount   DECIMAL(17,4) );

INSERTINTO tbl_user VALUES ('1', 'John', '100.00');

And the proposed function:

CREATEFUNCTION incrementAmount
  (p_id VARCHAR(100), p_amount DECIMAL(17,4))
RETURNSDECIMAL(17,4)
BEGINUPDATE 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

Solution 3:

We can also use:

UPDATE  tbl_user  SET id = LAST_INSERT_ID(id),  amount =2.4,user_id=4WHERE id =123;

//SELECT
$id =SELECT LAST_INSERT_ID();
SELECT amount,user_id FROM tbl_user  WHERE id = $id   LIMIT 1

Solution 4:

It is not possible with a single query, but you can combine multiple commands into a script and execute them with a single request to the database server.

Run this script:

"UPDATE tbl_user SET amount=amount-'$amount' WHERE id='".$id."';SELECT amount FROM tbl_user  WHERE id='".$id."'; "

Also, you might want to check whether $id is a number, as I do not see a protection against SQL injection inside your code. SQL injection is a serious threat, you would do better to prepare and protect yourself against it.

Solution 5:

Here would be the procedure

CREATEPROCEDURE UpdateAndSelect
(
    @amount MONEY,
    @idINT
)
ASBEGINUPDATE tbl_user
   SET amount =@amountWHERE id =@id
   LIMIT 1SELECT amount
   FROM tbl_user
   WHERE id =@id
   LIMIT 1END
GO

You would call this stored procedure by setting your variables (@amoutn and @id) and then calling:

exec UpdateAndSelect

Hope this helps solve your problem