[SOLVED] removing duplicate records..
Moderator: General Moderators
-
waskelton4
- Forum Contributor
- Posts: 132
- Joined: Mon Sep 09, 2002 6:42 pm
[SOLVED] removing duplicate records..
Hello all,
I have a database that i've accidentally created duplicate records in. even down to the timestamp field. there are probably 3000 so it's too much to weed out by hand. Is there a way for me to delete the duplicates without deleting both rows?
thanks
Will
I have a database that i've accidentally created duplicate records in. even down to the timestamp field. there are probably 3000 so it's too much to weed out by hand. Is there a way for me to delete the duplicates without deleting both rows?
thanks
Will
Last edited by waskelton4 on Fri Aug 26, 2005 10:14 am, edited 1 time in total.
using php you could select all the primary keys, and whatever value you want to check for duplicates, then build an array of records to delete then like that
Code: Select all
delete from table where `key` in (1,5,100,400,700)-
waskelton4
- Forum Contributor
- Posts: 132
- Joined: Mon Sep 09, 2002 6:42 pm
But wouldn't that delete both of the records and not just one of the copies? The table doesn't have an enforced primary key currently. (i know.. real bad) after i get all the duplicate records out I'm gonna create it.jshpro2 wrote:using php you could select all the primary keys, and whatever value you want to check for duplicates, then build an array of records to delete thenlike thatCode: Select all
delete from table where `key` in (1,5,100,400,700)
will
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
you should be able to find duplicates like so:
(untested)
Code: Select all
SELECT t1.*, COUNT(t1.`someField`) AS siblings FROM `table1` t1 NATURAL LEFT JOIN `table1` t2 GROUP BY `someField1`, `someField2` HAVING siblings > 1(untested)
-
waskelton4
- Forum Contributor
- Posts: 132
- Joined: Mon Sep 09, 2002 6:42 pm
Hrmm..feyd wrote:you should be able to find duplicates like so:(untested)Code: Select all
SELECT t1.*, COUNT(t1.`someField`) AS siblings FROM `table1` t1 NATURAL LEFT JOIN `table1` t2 GROUP BY `someField1`, `someField2` HAVING siblings > 1
I tried that above and the value of 'siblings' came out to be the square of what the value should have been. (i.e. all the 2s were 4s and the 3 was 9)
Would there be a solution similar to something like this..
Code: Select all
SELECT ID, Count(ID) as counter FROM `table` group by ID where counter > 1Code: Select all
SELECT ID, Count(ID) as counter FROM `table` group by ID where count(ID) > 1Thanks for the help all..
will
-
waskelton4
- Forum Contributor
- Posts: 132
- Joined: Mon Sep 09, 2002 6:42 pm
I've thought of similar ways but I was hoping to do it with just SQL...AGISB wrote:Just read all the entries and put it in an array. Filter out all duplicates and write it to a new table.
Empty the whole table and rewrite it with the other tables data. Could be by SQL or PHP
I think I will end up doing something similar to this.
Thanks for everyone's help!
will
-
waskelton4
- Forum Contributor
- Posts: 132
- Joined: Mon Sep 09, 2002 6:42 pm
Actually I think that > 1 works fine because the square of 1 is still 1.. so the lowest value returned is for the rows with one duplicate (2 rows) so that value is 4.feyd wrote:oops, change the > 1 to > 2. Yes they will be squares, because the join attaches to all instances of the duplicate for each one that exists
Thanks again for the help all..
Now I just have to shut down the application for a few minutes while I remove these things.. blech..
ws
-
waskelton4
- Forum Contributor
- Posts: 132
- Joined: Mon Sep 09, 2002 6:42 pm