MySQL to return updated value(s)

Question

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.

Possible duplicate: https://stackoverflow.com/questions/11477121/mysql-return-updated-rows — frozen, Jun 26 '17 at 19:27
you usually explicitly pass them to the update query. you already know them. — newtover, Jun 26 '17 at 19:27
@newtover yes I know the values I'm passing but I don't know the values that are already present in the row. — g3blv, Jun 26 '17 at 19:29
MySQL has one `UPDATE` statement. Only the dataset will determine whether you update zero, one, or more rows. — gaborsch, Jun 26 '17 at 19:32

gaborsch · Accepted Answer · 2017-06-27T12:25:43.283

2

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;

edited Jun 27 '17 at 12:25

answered Jun 26 '17 at 19:29

gaborsch

15,408
6
37
48

When `SET`ing `NEW.last_changed_values` is it possible to `SET` it to the `new.col1` value rather then just the column name (`col1`)? – g3blv Jun 27 '17 at 10:50
Sure, it is possibe. It was just an example. – gaborsch Jun 27 '17 at 10:52
In the [sqlfiddle](http://sqlfiddle.com/#!9/bcbab/1) tried with `SET NEW.last_changed_values = CONCAT_WS(',', IF(new.name = old.name, NULL, new.name));` but `new.name` returns nothing/empty. – g3blv Jun 27 '17 at 10:58
In the sqlfiddle the row #1 already contains 'Hans' as `name`. Try to update it to 'Peter', and you will see it working. – gaborsch Jun 27 '17 at 11:42

MySQL to return updated value(s)

1 Answers1