How to accomplish such insert-or-update strategy?

by Eugene   Last Updated October 14, 2018 20:06 PM

Programming stored procedures isn't my strongest point when it comes to programming, but I'm trying to get better. I've stumbled upon a business requirement that's kind of hard for me and I was wondering if anyone had already dealed with it in their career.

There's this data in a staging table that needs to be loaded in a production table with insert for the new data and insert-update(backup like) for the old data.

The sample of data in production table looks like this:

AssetID  | BeginDate | EndDate | Field1 | Field2 | FieldN
1        | (TS)      | (TS)    | 0.2351 | 1.3510 | 4.1234

Meanwhile in staging:

AssetID  | Field1 | Field2 | FieldN
1        | 0.2351 | 1.3510 | 4.1234

In prod the PK is AssetID and BeginDate meanwhile in staging is only AssetID(because the data is flushed every time).

The requirement is to write a stored procedure that when there are new assets in the staging area, they are inserted as new in prod(with BeginDate as CURRENT_TIMESTAMP and EndDate as NULL), meanwhile if there are data(in staging) that are different on the Field1,Field2 or FieldN fields from the production one, then a new record is written with the same AssetID but BeginDate as CURRENT_TIMESTAMP and EndDate as NULL, and the old record with the same combination (BeginDate memorized and EndDate is NULL) being updated with an EndDate as CURRENT_TIMESTAMP

Any of you have dealed with a similar task/requirement? If yes, could you share your solution please?



Answers 1


You have to try to finalize any existent opened record for the given AssetID before insertion of the new record.

BEGIN
  DECLARE ts TIMESTAMP = NOW();
  DECLARE f1, f2, fN FLOAT; 

  SELECT Field1, Field2, FieldN
    FROM prod_table
   WHERE AssetID = _givenID
     AND EndDate = NULL
    INTO f1, f2, fN
  ;

  IF ( f1 <> _field1 OR 
       f2 <> _field2 OR 
       fN <> _fieldN )
    THEN 
      UPDATE prod_table    -- Here we suppose there is zero or one opened record
         SET EndDate = ts  -- If there is no records for _givenID, update do nothing
       WHERE AssetID = _givenID
         AND EndDate IS NULL  
      ;

      INSERT prod_table (AssetID, BeginDate, EndDate, Field1, Field2, FieldN)
      VALUES ( _givenID, ts, NULL, _field1, _field2, _fieldN )
      ;
  END IF;
END
Kondybas
Kondybas
October 14, 2018 19:32 PM

Related Questions



Use LOOP variable for INSERT in MySQL

Updated May 28, 2017 04:06 AM

SP run without error but not data inserted

Updated November 13, 2017 06:06 AM