0

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

  • This cannot be achieved by a trigger(atleast in MySQL), you cannot update the same table you are inserting in a trigger. Second i didn't quite understand what you meant by "By hand (mannually)" – indago Apr 14 '15 at 7:39
0

This is beyond what SQL can do. Your application (which needs to be something more of a programming language than Excel) should orchestrate the task.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.