mysql更新列，然后选择更新的值 [英] mysql update column then select updated value

问题描述

我有一张这样的桌子

tbl_user

id
user_id
amount

首先，我想根据ID更新一行

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 ";

现在我想获取已应用此sql的金额列的更新值

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 ";

我的问题是我可以结合以上sql或任何单个查询来实现上述任务吗?

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

推荐答案

您可以模仿的最好的方法是使用两行查询，可能使用类似这样的变量:

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;

那么，您能做的最好的就是创建一个Stored Procedure，如:

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

然后在PHP中呼叫Stored Procedure

注意:PostgreSQL使用RETURNING语句具有如下所示的这种选项:

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

请参见此处

这篇关于mysql更新列，然后选择更新的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！