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...