Searching and deleting MySQL records after insert with stored procedure

Question

I have a quandary with which I am struggling to answer, and looking for some advice on how it can be done.

We have a table, that is basically comprised of the following fields. I have used Info x to describe three VARCHAR(30) fields, because business confidentiality means I cannot give them their accurate name.

• RefId (Int) - Links the entry to other records in the database
• YearId (Int) - Can be zero or above
• MonthId (Int) - Can be zero or above
• DayId (Int) - Can be zero or above
• Info A (Varchar 30) - Can be NULL
• Info B (Varchar 30) - Can be NULL
• Info C (Varchar 30) - Can be NULL

Now the date is split into three fields because it may be that we have a year and nothing else, it may be a year and month, or may be all three. Hence if we have June 1999 for the date for example it would be stored at Year ID - 1999, MonthId - 6, Day Id - 0.

The issue I have is as follows. Because of data coming from a third party, there may be duplicate info in the A, B, or C fields, but with a different date. For example two INSERT statement add the following details:

• 78142, 1999, 6, 0, NULL, ABC123, NULL
• 78142, 2004, 0, 0, NULL, XYZ127, NULL
• 78142, 2010, 4, 21, NULL, ABC123, NULL

In this case it is fine, no problem. Although there is duplicate data with a different date, the data for the specific RefID as changed in the mean time, so the third entry above is valid as it is updating the information store.

However, let us now imagine, a subsequent insert (which could be hours or months later) adds:

• 78142, 1996, 11, 14, NULL, ABC123, NULL

A query on the table for ReferenceID 78142, order by Year, Month, Day DESC therefore now gives the following results:

• 78142, 1996, 11, 14, NULL, ABC123, NULL
• 78142, 1999, 6, 0, NULL, ABC123, NULL
• 78142, 2004, 0, 0, NULL, XYZ127, 2351
• 78142, 2010, 4, 21, NULL, ABC123, NULL

Because we need to avoid repetition when the database was queried, we need to remove the duplicate record (the 2nd Entry in the above list) as the core data in the A,B,C fields has not changed since the previous entry (it is a duplicate). The last entry in the above list needs to remain however, as the core data is different from the previous entry - ie it has changed, then changed back.

My query is therefore how to do this in the most efficient way using a STORED PROCEDURE?

I had tried using CONCAT(Month,Year,Day) with LPAD to make sure the Month and Day were a two digit number, that would give the ability to sort on an eight digit date field WHERE the date of the next entry is greater than the date of the new entry.

Ie the stored procedure is passed vRefId, vYear, vMonth, vDay, vInfoA, vInfoB, vInfoC and basically does:

• INSERT INTO table (RefId, YearId, MonthId, DayId, InfoA, InfoB, InfoC) VALUES (vRefId, vYear, vMonth, vDay, vInfoA, vInfoB, vInfoC)

• It then needs to pull the next record for specified RefId, after YearId-MonthId-DayId and IF InfoA=vInfoA, InfoB=vInfoB and InfoC=vInfoC, then delete it. But I am getting stuck on how to get a stored procedure to compare details.

There has to be relatively quick and easy way for a stored procedure or function to deal with it and auto delete the duplicates. I can see how to remove records that match, but this function/procedure HAS TO GUARANTEE that it is only the next record after the date of the newly inserted one for this RefID that is deleted, and only then if it matches A, B, C fields. Grouping records does not work as that could ignore intermediate entries.

Sorry for the long post, but it was the only way I could clearly explain the problem. Any help, advice or pointers would be appreciated. I have been playing with this for three days now, and am no further forward.

Answer 1

For anyone reading this in the future. I have resolved it - after four days!

In essence, by inserting the new record using a stored procedure, during which we create a RecordDate by setting a RecordDate variable to the output from CONCAT(vYear,LPAD(vMonth,2,"0"),LPAD(vDay,2,"0") - the three date fields that are passed to the stored procedure. LPAD is necessary to ensure single digit months are shown as double digit.

After the insert, store the CONCAT_WS of the key fields (A, B, C) in a variable, then performing a SELECT with the same CONCAT_WS (but on the database fields) on the RefID, where the CONCAT(YearId,LPAD(MonthId,2,"0"),LPAD(DayId,2,"0") - the Date fields from the database this time - are greater than the RecordDate, and limit the returns to 1 record.

If the two CONCAT_WS variables match, then the data is the same regardless of the date, we preference the earlier date and DELETE the later date record.

What was throwing me out was that on occasions the InfoA, InfoB and/or InfoC fields could have a NULL value that WHERE InfoA="" in the SELECT statement was not finding. Having now ensured that the field is blank or has a value (ie never NULL) the above code now works.

While I am still sure there should be a more efficient way to compare to database records than two SELECT statements followed by a delete, this works!