[Resolved] deleting dupes from MySQL

perpetualshaun

I am trying to fix a list here after the fact...

I imported a list of codes that I received from a client, and it turns out that there were LOTS of duplicates in there. I know how to write this query to find the codes that are in there more than once:

SELECT code, count(*) AS num_dupes FROM reynolds_charge_codes GROUP BY code HAVING num_dupes > 1

However, I don't know how to write a MySQL statement that will delete the duplicate entries. Is there a statement that I can write, and run through PHP MyAdmin to clear out the dupes?

LordShryku

Depending on your mysql version, a subquery would work. But this also assumes you only want to delete one record. You may be better off getting the records and manipulating them in PHP.

DELETE FROM code
      WHERE code IN (SELECT code, 
                       FROM reynolds_charge_codes 
                     HAVING count(code) > 1)
      LIMIT 1

perpetualshaun

I am trying to run this query, on what is an up-to-date version of MySQL and PHP MyAdmin, but it's telling me that it deleted 0 rows:

DELETE FROM charge_codes WHERE code IN ('SELECT code FROM charge_codes GROUP BY code HAVING count(*) > 1') LIMIT 1

Yet, when I run just this query alone to see how many codes are in there more than once, I get 111 matches back. Any thoughts as to what I'm missing here?

SELECT code FROM charge_codes GROUP BY code HAVING count(*) > 1

LordShryku

Don't put single quotes around the subquery.

perpetualshaun

Still no luck. This is exactly what I'm running:

DELETE FROM charge_codes WHERE code IN (

SELECT code
FROM charge_codes
GROUP BY code
HAVING count( * ) >1
)
LIMIT 1

I get this from phpMyAdmin 2.5.4-rc2:

MySQL said:

#1064 - You have an error in your SQL syntax near 'SELECT code
FROM charge_codes
GROUP BY code
HAVING count( * ) >1 )
' at line 1

This is running MySQL 3.23.58

LordShryku

3.23 is not current stable release. 4.0 is, and the ablity to subquery was just added to this release. So your version won't work with subqueries. You're going to need to find an alternate method with multiple queries to do this.

perpetualshaun

Well, I spent some time yesterday upgrading MySQL and my phpMyAdmin, and this query still isn't working:

DELETE FROM charge_codes WHERE code IN (

SELECT code
FROM charge_codes
GROUP BY code
HAVING count( * ) >1
)
LIMIT 1

I get this error:

MySQL said:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT code
FROM reynolds_charge_codes
GROUP BY code
HAVING co

Any ideas? I realize I could have and still could write a PHP script to accomplish the same thing in far less time, but I want to try and do this directly in MySQL and learn a little something new.

Thanks