I have a database where each entity has a unique ID, but recently all of the entity names were changed (not in the same way) to improve readability. This has made a lot of the summary reports look like the following:

ID     Name            Total for the year     
3011   Joesmith          3000
3011   Joe Smith         1000
5024   DBS               400
5024   Deborah Smith     150

What's the best way of going about either correcting the old entries to match the new ones or simply displaying the new name for any matching ID? At the end of the day we can get the summaries we're interested in by just using the ID's, but ultimately we want to interpret those by seeing our names.

We're looking at about 1000 or so IDs with different names and maybe 50,000 or so rows in total.

  • 2
    1) How do you know which entry is "newer"? (it's obvious in your example, but how do you know in the db?), 2) The "Total for the year" values are different for the same ID. What is the "correct" value for each ID?
    – user3856
    Oct 31, 2017 at 17:28
  • @BarryCarter, 2). It seems they change "Joesmith" to "Joe Smith" in some places, but not in all places. I suppose the "Total for the year" value should be 4000. 1). The task of detecting the direction of editing automatically looks interesting... Oct 31, 2017 at 19:43
  • Probably should be migrated to SO or somewhere. Also, more examples of "readability improvements" are needed... Oct 31, 2017 at 19:47
  • 1
    1) that's a good question. There is a third column with a transaction date and so you could hypothetically take any entry after a certain transaction date to determine newer. 2) The "total for the year" is the total under the ID. If you try to organize by name you get that ugly split.
    – nerb
    Oct 31, 2017 at 21:08
  • Please edit your question so that it contains all necessary information (the additional texts from your comments). It probably helps if you also write what database the data is in and what tools and skills you have available.
    – user4293
    Nov 1, 2017 at 9:28

1 Answer 1


It's a bit strange that the same unique ID can be associated with more than one name. The database schema must have a design problem. One solution might be to clean up your 50,000 transactions one day by standardizing the names associated with the IDs. Software like Open Refine can help you to normalize your data.

enter image description here


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.