I'm having some trouble importing data into a table that I'm not sure how to describe succintly enough that can I can properly search for it (not that I haven't tried).
I have a table with three columns (actually more like 150, but these are the ones that matter):
order_number, date, status
that I update with some CSV data I don't control.
Status can have 5 states: 'pending', 'started', 'cancelled', 'not done' and 'complete'.
Each order will start 'pending', then progress to 'started', and finally end at 'cancelled', 'not done' or 'complete'.
If they end at 'cancelled' or 'complete', there's no problem, I just keep updating the status for the day and that's it.
The problem is when the status ends at 'not done'.
Usually when that happens, there's a new attempt only the next day, so I can just use the date to differentiate between attempts, but frequently enough to be a problem, a new attempt is made on the same day, and I need to keep both the first attempt, and the new one (that can still fail).
There's no field meant specifically for differentiating the attempts, nor changes to the order number, the time fields I could possibly try to use are populated with estimated times until the attempt is finished, and the only reason I know there was more than one attempt is because I get two rows for the same order on the same day.
Currently I'm loading the data into a temporary table, then using "INSERT...ON DUPLICATE KEY UPDATE" with a unique key on order_number and date to load it into its proper place, but that's causing me some loss of data.
I've tried adding a calculated field with value based on the final status to the index, but then I got at least two rows for each order: one for the final status, and one for 'started'.
I'm looking for some ideas for how I can solve it without processing the data outside the database before insert, since it would take awhile on the limited hardware available at the moment.