By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,041 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

How to eliminate duplicate IDs from a table

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a

"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

P: n/a

"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.