I want to update a specific table but in the following manner. When a record is completely the same (so not only the primary key, but all fields) then I don't want any changes (or a new record inserted, etc). It should ignore the record, because nothing has changed. But, when one of the fields of in a record IS changed, then I want the existing record to be closed with an end date in the field end_date and an new record with a start date and a new follownumber (eg. 02). The end_date of the old record is the start_date of the new record. How do I get this done within MySQL? By hand (manually) and automatically (trigger?). Both would be nice to know. In the end automatically would be the best thing. But I want to understand what happens and why. I really hope someone can help me or show me the right direction. Thanks.
With 'by hand' I mean when I have a existing table and get a .csv file with possible updates. Put this in a temporary table of it's own and then with a sql statement I insert new records, close existing records (and then insert end_date, etc) that have an update record in the temporary table and then last insert the update records from the temporary table with a new subsequent follownumber. After all is done then I delete the temporary table.
If it's not completely clear, I've created a file of how the results should look in Excel: mysql_test_expert_updating.xlsx
I only use Excel as a presentation tool and use MySQL code to get the data from the table. So the Excel I've added is only to show how the content of the table should look...
Kind regards,
Vincent
