I have this 'table A'. It contains two columns; email and email_alias. A sample is below:
= email | email_alias =
= firstname.lastname@example.org | email@example.com =
= firstname.lastname@example.org | email@example.com =
= firstname.lastname@example.org | email@example.com =
= firstname.lastname@example.org | email@example.com =
= firstname.lastname@example.org | email@example.com =
= firstname.lastname@example.org | email@example.com =
= firstname.lastname@example.org | email@example.com =
As you can see only the alias need be unique. Anyways, i have a script that will allow a user to update this table. Previously, i would truncate the entire table, and then reinsert the latest entries.
Problem: It's possible for a user to see an empty or incomplete list.
My next thought, was to flag all of the old fields "to be deleted", insert the new, and then go back and delete the flagged entries.
Problem: It's possible for the user to see duplicate entries.
I could say
if email = new.email & email_alias = new.email_alias, then do nothing, else, add record.
Problem: Old records will still be present
I would rather not do this, but is my only option to create a temporary table? Remove the records from the old table that don't exist in the temp table, and add records from the temp table that don't exist in the old table?