CODEWITHSUNDEEP
mysqlsql
Satish Sharma
Satish Sharma

Reputation: 9625

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

Upvotes: 31

Views: 58749

Answers (5)

Gauttam Jada
Gauttam Jada

Reputation: 598

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

Upvotes: 2

Edper
Edper

Reputation: 9322

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

Upvotes: 44

Joshua Huber
Joshua Huber

Reputation: 3533

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

Upvotes: 9

Lemuel Botha
Lemuel Botha

Reputation: 659

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

Upvotes: 1

Lajos Arpad
Lajos Arpad

Reputation: 76414

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.

Upvotes: 2

Related Questions