Use MySQL trigger to update another table when duplicate key found
- by Jason
Been scratching my head on this one, hoping one of you kind people and direct me towards solving this problem.
I have a mysql table of customers, it contains a lot of data, but for the purpose of this question, we only need to worry about 4 columns 'ID', 'Firstname', 'Lastname', 'Postcode'
Problem is, the table contains a lot of duplicated customers.
A new table is being created where each customer is unique and for us, we decide a unique customer is based on 'Firstname', 'Lastname' and 'Postcode'
However, (this is the important bit) we need to ensure each new "unique" customer record also can be matched to the original multiple entries of that customer in the original table.
I believe the best way to do this is to have a third table, that has 'NewUniqueID', 'OldCustomerID'. So we can search this table for 'NewUniqueID' = '123' and it would return multiple 'OldCustomerID' values where appropriate.
I am hoping to make this work using a trigger and the on duplicate key syntax. So what would happen is as follows:
An query is run taking the old customer table and inserting it in to the new unique table. (A standard Insert Select query)
On duplicate key continue adding records, but add one entry in to the third table noting the 'NewUniqueID' that duped along with the 'OldCustomerID' of the record we were trying to insert.
Hope this makes sense, my apologies if it isn't clear.
I welcome and appreciate any thoughts on this one!
Many thanks
Jason
