How to accomplish such insert-or-update strategy? 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 dealt 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 dealt with a similar task/requirement? If yes, could you share your solution please?
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 &lt;&gt; _field1 OR 
       f2 &lt;&gt; _field2 OR 
       fN &lt;&gt; _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