MySQL update field on insert if other field (not key) changed

Question

I'm relatively confused about this...

I've got a table like:

+----------------+--------------------------------------------------+------+-----+-------------------+-----------------------------+
| Field          | Type                                             | Null | Key | Default           | Extra                       |
+----------------+--------------------------------------------------+------+-----+-------------------+-----------------------------+
| dhcp           | int(10) unsigned                                 | NO   | PRI | 0                 |                             |
| ip             | int(10) unsigned                                 | NO   | PRI | 0                 |                             |
| mac            | varchar(17)                                      | YES  | MUL | NULL              |                             |
| lease          | enum('fr','a','fi','a','u') | NO   | MUL | free) |                                |                             |
| date           | timestamp                                        | NO   | MUL | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| uid            | varchar(255)                                     | YES  |     | NULL              |                             |
| starts_date    | datetime                                         | YES  |     | NULL              |                             |
| starts_weekday | tinyint(1)                                       | YES  |     | NULL              |                             |
| ends_date      | datetime                                         | YES  |     | NULL              |                             |
| ends_weekday   | tinyint(1)                                       | YES  |     | NULL              |                             |
| first_seen     | datetime                                         | YES  |     | NULL              |                             |
+----------------+--------------------------------------------------+------+-----+-------------------+-----------------------------+

I just added the first_seen column. The idea is that I will use INSERT... ON DUPLICATE KEY UPDATE. dhcp and ip are rightly primary keys, as I want to only have one record for them at a time. So if mac changes, it should update the existing row if one exists for that dhcp, ip combination.

however, I want to have first_seen updated every time a (ip, dhcp, mac) combination is seen... i.e. if the value of 'mac' changes, I want to update first_seen. If the value of 'mac' stays the same, I want to leave first_seen the same.

Is there any simple way to do this in SQL... i.e. an IF() or something? or do I have to handle this with another SELECT in the PHP script (keeping in mind that we're parsing a file to get this data, and inserting abut 10-16k rows, so time is a factor).

Thanks, Jason

Answer 1

I think you might want to use the TIMESTAMP column in MySQL - this will change when the record is updated or inserted. It will change when any value in the row changes however. If you want something else to happen you might consider a trigger that fires after update and validates the other business logic you referenced.

Answer 2

You could use a MySQL trigger for this.

Answer 3

Have you considered using triggers? That's a MySQL server-side event happening when some other event, such as update of the mac column, happens.

Answer 4

Thanks for all the suggestions, guys. It turns out that I don't need a trigger for this, just an IF at the beginning of the update statement.

in the INSERT INTO... I have first_seen=CURRENT_TIMESTAMP

Then at the beginning of the UPDATE I have

first_seen = IF( mac != VAULES(MAC), CURRENT_TIMESTAMP, first_seen)

This only updates first_seen if the mac changes, else it sets first_seen to itself (its current value).