2

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.

4
  • It seems, as if you should keep those same day records in the temporary table, for which the final one is marked not done. (If this breaks your overall logic, move them to a different table.) And you will need to further adjust your processing of not done records to reconcile the finally proper one with these stray ones. The exact procedure does depend on your business requirements.
    – Abecee
    Dec 14, 2019 at 7:25
  • If - as per one of your comments - you need to keep track of the reason(s) for NOT DONE, you might want to consider an additional table, which stores the relevant pieces of information for as long as needed.
    – Abecee
    Dec 14, 2019 at 11:48
  • Do you still need a hand with this?
    – Abecee
    Dec 26, 2019 at 11:52
  • @Abecee Because I was having too much trouble with it, my boss changed the requirements, so I'm currently using three tables: one for the 'complete' status, one for 'not done', and one for the others that is reset everyday (after making a report with anything that didn' end the the day either completed or or 'not done')
    – Carlos
    Dec 28, 2019 at 0:34

1 Answer 1

1

MySQL is rusty so bear with me

You are on the right path using a staging table to import the CSV file, but you going to need to break the Inserts and the Update into separate steps

Not stated but i'm going to assume the import may have specific Order show up for each status changed so the order may show up 5 or more times from the Import and that the CSV file is created serially meaning PENDING, STARTED, NOT DONE, (CANCELED, COMPLETE) would appear in that order never in a mixed order. Cancel Showing before Pending or Start. If that is the case order the Update may need to fiddled with

Create ImportTable (Serial_ID INT NOT NULL AUTO_INCREMENT,
"columns from the CSV" )

Created an ID field so the order of the CSV is maintained by the import.

Import the CSV. Not sure this is the correct MySQL code to Import and set a column to a different value

LOAD DATA INFILE 'import.csv'
INTO TABLE ImportTable
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(@id, "lis of columns ")
SET id =nextval('Serial_ID)  ;

Second step get all the new orders we are going to ignore the status for the first import

Insert into OrderTable 
(Select Distinct "columns to insert", 'importing' as OrderStatus 
 from ImportTable where Order_id not in 
   (Select Order_id from OrderTable ) 
)

Now have all the orders in the system lets just process the status changes serially Don't need the CASE statement for Order_Status unless the CSV order is all random..

DELIMITER $$
CREATE FUNCTION process_import_status() returns bool
BEGIN
DECLARE @(list of vars for COLUMNS to Update)
DECLARE updates_cursor CURSOR FOR
 SELECT "columns to update" 
  CASE Order_status
    WHEN 'pending' THEN 1
    WHEN 'started' THEN 2
    WHEN 'not done' THEN 3
    WHEN 'cancelled' THEN 4
    WHEN 'complete' THEN 5 
 END as 'sort_order
 FROM ImportTable order by sort_order, Serial_ID, date;

updates_loop  Loop:
  FETCH  updates_cursor INTO @(list of vars for COLUMNS to Update);
  update OrderTable SET "list of columns"= @(LIST of COLUMNS) 
     where Order_id = @order_id ;
END LOOP  updates_loop;
END $$$
DELIMITER ;

This should get you close to what you need to do..

5
  • So you first pull all records from ImportTable into Orders and then you run again over the ImportTable?
    – Abecee
    Dec 13, 2019 at 21:33
  • yes, setting all the status changes serially, There is not allot of details to work with here, and if there are other issues . He stated he is missing records from the filtering and not clear why the records are missing... I think there are several problems with the import. breaking it down into separate steps will help identify those issues, then it can be sped up and tweaked
    – zsheep
    Dec 13, 2019 at 21:47
  • I agree with taking one step at a time. But I‘d be very reluctant to have several records in the Orders table representing a single order. - In other words: Do not move all records from ImportTable indiscriminately.
    – Abecee
    Dec 13, 2019 at 22:09
  • i have a distinct in the select and set all order_status = 'importing', so assuming the only change out of 150 columns is status (highly unlikely) this should only return 1 copy of the order from the ImportTable
    – zsheep
    Dec 13, 2019 at 23:31
  • Thanks for the reply, I just got home and have yet to parse it fully. To clarify a bit, usually, on each import, most order show up only once, with their current status, except when there are two (or more) tries to complete it, when it will have at one row for 'NOT DONE', and another one for a new current status, until completed, cancelled, or NOT DONE again (rare). I'm missing records because with my first attempt, I updated the rows with NOT NDONE status with the newest try,but with it I lose information (like the reason it failed).
    – Carlos
    Dec 14, 2019 at 0:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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