Stefan Krzywicki (10/8/2013)
Greg Edwards-268690 (10/8/2013)
I can see the need to understand how to do this, although I tend to go back to the basics.
If you find yourself needing to do this, question if it be designed into the the table in the first place to prevent this?
After all, I think this leads into the initial issue - without deleting all, the engine needs a way to discern which one(s) to delete.
I realize sometimes you have no input / control into this, but a few words about this concept might be a worthwhile addition.
If you wanted to expand this, age (at least to me) should be calculated, not stored in most cases.
And the changing job title also drives me towards separating out to different tables and having effectivity dates.
But that is way beyond your intended scope.
Just trying to spark a thought or two, not to make a big deal about any of this.
It is funny, I agonized a bit over the example table. As I was putting in the columns I kept thinking "Well, if this were a real database I'd put this in another table so it could have multiple values or historical data or maybe this should be calculated" then I reminded myself that was outside the scope of this article and I just needed something to use as an example. : -)
Same thing with table design, there are situations where you have no way to prevent this ahead of time, whether it is because the duplicates are in the data coming in or because the table is already in production and the powers that be won't approve a structural change. I wanted to keep the focus on this one task as dealing with every possibility would make the article far longer.
I thought you would have those mixed thoughts and just kept it simple. 🙂
Part of my comment was driven by how often we can see this poor design, something we shouldn't have to run into, but in reality do many times. So we have to deal with it.
Kind of hit a nerve also based on my toughts of Select DISTINCT, which seemed to be common for user queries where I used to work.
Especially when I knew there was no need for this, they were just missing some file joins, and inviting performance and data issues unknowingly.
But good article for the limited scope.