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?

Do you want to in mysql or have you use any front end framework? — Sadikhasan, Jul 11 '14 at 06:47
both update and select. first update the column with decrement some dynamic value then select the new value — Satish Sharma, Jul 11 '14 at 06:53
Using variables inside your SQL query most likely is vulnerable to SQL injection attacks. Consider [PDO](https://www.php.net/manual/en/book.pdo.php) — undefined, Oct 03 '21 at 10:16

score 43 · Accepted Answer · edited Jul 08 '16 at 12:59

43

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

edited Jul 08 '16 at 12:59

fred727

2,644
1
20
16

answered Jul 17 '14 at 09:21

Edper

9,144
1
27
46

I bench the 1st solution (UPDATE with @var and SELECT @var) and it is only 2-5% faster than doing 2 requests like exposed in the question. So I am not sure it is good to use such improvement because your SQL become non-standard... – fred727 Jul 08 '16 at 13:29
1

@fred727 So, how do you propose an improvement based on OP's exact requirements, namely, combining two lines of queries into one? – Edper Jul 08 '16 at 15:01
you can use like this : UPDATE TABLE SET columnname = columnname + 1; select columnname from TABLE – Parthpatel1105 Jul 13 '16 at 06:40
Will the single query approach handles simultaneous updates from different processes without any side effect? – toyvenu Sep 06 '17 at 07:30
1

This is probably the worst solution of the list. Not only is it doing another select, granted not using a table but still needs processing, like the other proposed solutions, the data may not reflect whats actually in the database due to triggers. This solution will fail if there is or the DBA decides to add a trigger later on that modifies the field on update. It also assumes that the where clause is a unique field, which you are only going to get the last updated value. – Rahly Aug 28 '21 at 23:37

Joshua Huber · Answer 2 · 2014-07-17T13:49:32.580

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:

Realized after I posted this that almost the same question is here: http://stackoverflow.com/a/7446154/1680777 — Joshua Huber, Jul 17 '14 at 13:45

score 2 · Answer 3 · answered Jul 11 '14 at 07:13

2

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.

answered Jul 11 '14 at 07:13

Lajos Arpad

64,414
37
100
175

can we use procedure? – Satish Sharma Jul 11 '14 at 08:46
Of course you can. If you want to do so, then create the procedure which expects a value for id and put the script inside the procedure. – Lajos Arpad Jul 11 '14 at 09:02
is procedure takes much higher execution time than a sql? – Satish Sharma Jul 11 '14 at 09:09
No, it does not. In fact if you need to use it in multiple places then a procedure is advisable. – Lajos Arpad Jul 11 '14 at 09:53

score 2 · Answer 4 · edited Aug 11 '19 at 07:44

2

We can also use:

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

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

edited Aug 11 '19 at 07:44

NelsonGon

13,015
7
27
57

answered Jul 17 '14 at 11:00

Gauttam Jada

588
4
7

This is probably the closest answer, however this still has an atomic problem. – Rahly Aug 28 '21 at 23:29

score 1 · Answer 5 · answered Jul 17 '14 at 09:22

1

Here would be the procedure

CREATE PROCEDURE UpdateAndSelect
(
    @amount MONEY,
    @id INT
)
AS
BEGIN

   UPDATE tbl_user
   SET amount = @amount
   WHERE id = @id
   LIMIT 1

   SELECT amount
   FROM tbl_user
   WHERE id = @id
   LIMIT 1

END
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

answered Jul 17 '14 at 09:22

Lemuel Botha

659
8
22

1

I don't think that is valid syntax for MySQL. Looks like T-SQL to me (for Microsoft SQL Server). – Jul 17 '14 at 14:03
Oh actually, that is T-SQL, my mistake – Lemuel Botha Jul 17 '14 at 14:12

mysql update column then select updated value

5 Answers5

Linked