Get Updated Value in MySQL Instead of Affected Rows

Get Updated Value in MySQL instead of affected rows

You can do it with a stored procedure that updates, and then selects the new value into an output parameter.
The following returns one column new_score with the new value.

DELIMITER $$   -- Change DELIMITER in order to use ; withn the procedure
CREATE PROCEDURE increment_score
(
   IN id_in INT
)
BEGIN
    UPDATE item SET score = score + 1 WHERE id = id_in;
    SELECT score AS new_score FROM item WHERE id = id_in;
END
$$            -- Finish CREATE PROCEDURE statement
DELIMITER ;   -- Reset DELIMITER to standard ;

In PHP:

$result = mysql_query("CALL increment_score($id)");
$row = mysql_fetch_array($result);
echo $row['new_score'];

mysql update column then select updated value

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

MySQL no affected rows upon UPDATE when value not changed

According to mysql documentation, you can change the behaviour of affected_rows by passing the MYSQLI_CLIENT_FOUND_ROWS flags while connecting using mysql_real_connect.

In this case, mysql_affected_rows returns the number of rows matched by the WHERE condition, not the number of updated rows.

MySQL after update trigger with number of affected rows condition

Pattern:

CREATE TRIGGER ...
...
BEGIN
INSERT INTO user_log (user_id,action,old_data,new_data)
SELECT NEW.user_id,
       CONCAT('Changed ', OLD.name, "'s ", columnname),
       oldvalue,
       newvalue
FROM ( SELECT 'name' columnname, OLD.name oldvalue, NEW.name newvalue
       UNION ALL
       SELECT 'address', OLD.address, NEW.address
       UNION ALL
       SELECT 'city', OLD.city, NEW.city
       UNION ALL
       SELECT 'phone', OLD.phone, NEW.phone
     ) data
WHERE NOT oldvalue <=> newvalue;       
END;

Also you may use ROW() constructor instead of SELECT .. UNION ALL.

https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=c63b122abedf9481d72129bee0d2d87d

mySQL UPDATE query returns 0 rows affected

If the such value already exists, mysql won't change it and will therefore return "0 rows affected". So be sure to also check the current value of called

---

Related Topics