469,900 Members | 1,698 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,900 developers. It's quick & easy.

How to eliminate duplicate IDs from a table

Hello.

I've got a 'books' table with 'id', 'name', 'author' and
'isbn' fields.

Due to an error, my books are duplicated, so I've got (e.g.):
430 - Moby Dick - Herman Melville - 0192833855
430 - Moby Dick - Herman Melville - 0192833855
528 - A Tale of Two Cities - Charles Dickens - 0141439602
528 - A Tale of Two Cities - Charles Dickens - 0141439602

And when trying to find a book by its name, I get two
entries instead of one.

I would like:
1) To set id as "unique id"
2) To temove the duplicates IDs from the table

Any suggestion? Best regards and thank you very much.
Jul 19 '05 #1
2 4473

"Francesco Moi" <fr**********@europe.com> wrote in message
news:5b**************************@posting.google.c om...
Hello.

I've got a 'books' table with 'id', 'name', 'author' and
'isbn' fields.

Due to an error, my books are duplicated, so I've got (e.g.):
430 - Moby Dick - Herman Melville - 0192833855
430 - Moby Dick - Herman Melville - 0192833855
528 - A Tale of Two Cities - Charles Dickens - 0141439602
528 - A Tale of Two Cities - Charles Dickens - 0141439602

And when trying to find a book by its name, I get two
entries instead of one.

I would like:
1) To set id as "unique id"
2) To temove the duplicates IDs from the table

Any suggestion? Best regards and thank you very much.


I'm reasonably new to MySQL (six months) but here is my twopence worth...

One solution (which wouldn't remove the duplicate keys) is to perform your
select query as you are now, but put a "GROUP BY ISBN" at the end of your
query statement... This should remove duplicate records being returned... I
think...

but if I had to do what you needed to do (and I stress *I* as this solution
may not be attractive in a large database environment) , I would make a copy
of the table - same structure, but have ID and ISBN in the new table set as
unique... I can program in PHP so I'd have it read your original table and
insert one record at a time in to your new table - duplicate INSERTs would
automatically then be ignored in the new table - I'd then replace the old
table with the new one... its not sexy, but it would work... however a large
database and/or a busy system could make this solution very very time
consuming.

--
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet?
Jul 19 '05 #2

"Francesco Moi" <fr**********@europe.com> wrote in message
news:5b**************************@posting.google.c om...
Hello.

I've got a 'books' table with 'id', 'name', 'author' and
'isbn' fields.

Due to an error, my books are duplicated, so I've got (e.g.):
430 - Moby Dick - Herman Melville - 0192833855
430 - Moby Dick - Herman Melville - 0192833855
528 - A Tale of Two Cities - Charles Dickens - 0141439602
528 - A Tale of Two Cities - Charles Dickens - 0141439602

And when trying to find a book by its name, I get two
entries instead of one.

I would like:
1) To set id as "unique id"
2) To temove the duplicates IDs from the table

Any suggestion? Best regards and thank you very much.


I'm reasonably new to MySQL (six months) but here is my twopence worth...

One solution (which wouldn't remove the duplicate keys) is to perform your
select query as you are now, but put a "GROUP BY ISBN" at the end of your
query statement... This should remove duplicate records being returned... I
think...

but if I had to do what you needed to do (and I stress *I* as this solution
may not be attractive in a large database environment) , I would make a copy
of the table - same structure, but have ID and ISBN in the new table set as
unique... I can program in PHP so I'd have it read your original table and
insert one record at a time in to your new table - duplicate INSERTs would
automatically then be ignored in the new table - I'd then replace the old
table with the new one... its not sexy, but it would work... however a large
database and/or a busy system could make this solution very very time
consuming.

--
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet?
Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Julio Allegue | last post: by
3 posts views Thread by Tony Young | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.