A
A
Andrey Strelkov2020-03-03 11:26:15
MySQL
Andrey Strelkov, 2020-03-03 11:26:15

How to insert records from one table into another, check one of the fields along the way, and update the other field when it changes?

Good afternoon, please tell me, there are 2 tables (more precisely, the first is a view, the second table):

DONOR
id ; full_name ; phone ; hash

RECIPIENT
id ; full_name ; phone ; hash ; date_changed


In the second table, id is the primary key, the task is such
that according to the schedule I need to insert data from DONOR into RECIPIENT
1) at the same time, if the id field already has such a value in RECIPIENT, then check whether their hash field
matches * if it does not match , then update the full_name, phone, hash fields in RECIPIENT with values ​​from DONOR AND ALSO! in the date_changed field, update the value to the current UNIX_TIMESTAMP
* if it matches, then ignore it
2) if there is no such id in the RECIPIENT table yet, then simply insert the values ​​from DONOR, and insert the current UNIX_TIMESTAMP into date_changed. The

usual kind of synchronization seems to be easy to do using INSERT ON DUPLICATE KEY UPDATE
but it turns out that I still need to add a little logic, which, when inserting new values ​​(not yet existing), updated the date_changed field with the current date, and also updated the date_changed field when updating existing records and hash fields that differed from them

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question