CODEWITHSUNDEEP
mysql
g3blv
g3blv

Reputation: 4367

MySQL to return updated value(s)

When updating an explicit row in MySQL is there anyway to get the UPDATE query to return the value(s) that was actually updated?

+----+-------+---------------------+
| id | name  | last_changed_values |
+----+-------+---------------------+
|  1 | Hans  | (null)              |
|  2 | Joe   | (null)              |
|  3 | Ralph | (null)              |
+----+-------+---------------------+

UPDATE user SET user_name = "Bertil" WHERE user_id = 1 would enter Bertil in last_changed_values

+----+-------+---------------------+
| id | name  | last_changed_values |
+----+-------+---------------------+
|  1 | Bertil| Bertil              |
|  2 | Joe   | (null)              |
|  3 | Ralph | (null)              |
+----+-------+---------------------+

With the help of GaborSch I've created this sqlfiddle.

CREATE TRIGGER names_BU BEFORE UPDATE ON `names`
FOR EACH ROW BEGIN      
SET NEW.last_changed_values = CONCAT_WS(',', IF(new.name = old.name, NULL, new.name));  
END/

But this doesn't SET last_changed_values to new.name (in this case Bertil). Is there some way of picking up the new value?

Update Seems like the stored procedure was case sensitive. Changed to 

SET NEW.last_changed_values = CONCAT_WS(',', IF(NEW.name = OLD.name, NULL, NEW.name));

Works as expected.

Upvotes: 3

Views: 13151

Answers (1)

gaborsch
gaborsch

Reputation: 15748

No, there's no such option in MySQL. Remember, that an update can change several rows with one instruction.

However, you can create a BEFORE UPDATE trigger, and compare the values one-by-one, and save the changed column names to a last_changed_values column, something like this:

ALTER TABLE mytable ADD COLUMN last_changed_values VARCHAR(200);

DELIMITER $$    
CREATE TRIGGER `mytable_BU` BEFORE UPDATE ON `mytable`
    FOR EACH ROW BEGIN      
    SET NEW.last_changed_values = CONCAT_WS(',', IF(new.col1 = old.col1, NULL, 'col1'), IF(new.col2 = old.col2, NULL, 'col2'), IF(new.col3 = old.col3, NULL, 'col3'));  
    END$$
DELIMITER ;

If you execute a statement like

UPDATE mytable SET col1=..., col2=...
WHERE id=1;

then you can query it like 

SELECT last_changed_values FROM mytable 
WHERE id=1;

Upvotes: 3

Related Questions