MySQL record matched but not updated

Chris Utter 0
Viewed 28

I use a created and updated field in most of my MySQL tables for obvious reasons.

In some instances I would like to be able to add a matched field which would indicate a record was found and attempted to be updated but no changes were made because there were no fields to update.

Basically to determine the "freshness" of a record, ie if a record hasn't been matched in a predetermined amount of time then it needs to be deleted. Is there an elegant way of doing this in MySQL?

mysql
1 Answers

If you have a definition like this -

CREATE TABLE `t` (
  `id` int(11) DEFAULT NULL,
  `email` varchar(20) DEFAULT NULL,
  `UPDATED_DT` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And you submit an update which finds nothing to change then the updated_dt won't change. However a before update trigger will fire so you have the opportunity to change NEW.updated_dt

create trigger t before update on t
for each row
    set new.updated_dt = current_timestamp;


Select * from t;

+------+---------------+---------------------+
| id   | email         | UPDATED_DT          |
+------+---------------+---------------------+
|    1 | AAA           | 2022-09-22 15:54:43 |
|    2 | b@example.com | 2022-09-22 15:50:52 |
|    3 | c@example.com | 2022-09-22 15:50:52 |
|    4 | d@example.com | 2022-09-22 15:50:52 |
+------+---------------+---------------------+
4 rows in set (0.001 sec)

UPDATE T SET EMAIL = 'AAA' where id = 1;

select * from t;

+------+---------------+---------------------+
| id   | email         | UPDATED_DT          |
+------+---------------+---------------------+
|    1 | AAA           | 2022-09-22 15:59:38 |
|    2 | b@example.com | 2022-09-22 15:50:52 |
|    3 | c@example.com | 2022-09-22 15:50:52 |
|    4 | d@example.com | 2022-09-22 15:50:52 |
+------+---------------+---------------------+
4 rows in set (0.001 sec)
P.Salmon0
Related