How can I identify and delete these duplicates?
I've got a table that looks like this
id contestant_id voter_id contest_end_date ballots
In some instances, I'm getting entries like this:
1 12345 0000 2012-09-30 1
2 12345 0000 2012-09-30 1
3 67890 1111 2012-09-30 4
Entry #3 is gold, but the first two represent duplicates that I need to delete. So, in light of the fact that the duplicates are defined in terms of two columns rather than just one, how do I identify them and then how do I delete them in the context of a loop / delete dynamic?
id contestant_id voter_id contest_end_date ballots
In some instances, I'm getting entries like this:
1 12345 0000 2012-09-30 1
2 12345 0000 2012-09-30 1
3 67890 1111 2012-09-30 4
Entry #3 is gold, but the first two represent duplicates that I need to delete. So, in light of the fact that the duplicates are defined in terms of two columns rather than just one, how do I identify them and then how do I delete them in the context of a loop / delete dynamic?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Do you want to delete all of the duplicates or all but one of the duplicates?
To access two columns in the array, you would probably want to concatenate the data elements.
To access two columns in the array, you would probably want to concatenate the data elements.
ASKER
All but one of the duplicates would be ideal.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Don't think you can get simpler than this, though it does create a new table.
CREATE TABLE new_table as
SELECT * FROM mytable WHERE 1 GROUP BY concat(contestant_id,voter
Don't run on live data until you've verified it is working correctly
CREATE TABLE new_table as
SELECT * FROM mytable WHERE 1 GROUP BY concat(contestant_id,voter
Don't run on live data until you've verified it is working correctly
Running the first section of my original post will give you the records that have duplicates, and you can use that to determine which ones to delete.
@GaryC123: Since your script at ID: 38378088 creates a new table, there is no real risk of running it on live data ;-)
doh lmao
but better safe than sorry....
but better safe than sorry....
Roger that!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I got it! Thanks for the feedback!
What are the two columns that need to be duplicates?