Delete duplicate records in MySQL

pda4me
pda4me used Ask the Experts™
on
I am using the following query to find duplicate records and have verified it runs correctly.  How do I alter this query to delete the records so that there is only one instance of the record?
SELECT MLS_LISTING_ID, STREET_NUMBER, STREET_NAME, UNIT_NUMBER, MLS_ID, SALE_PRICE, ZIP_CODE, TLN_REALTOR_ID, COUNT(mls_id)
FROM idx_FM_BO_NA
GROUP BY TLN_REALTOR_ID, STREET_NUMBER, STREET_NAME, UNIT_NUMBER, SALE_PRICE
HAVING COUNT(distinct MLS_ID) > 1; 

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Yes, but I need to keep one instance of the duplicate in the table
ok..follow the steps in the above URL, it will give you the unique records from your duplicate values.
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

awking00Information Technology Specialist

Commented:
CREATE TABLE no_dupes AS
SELECT DISTINCT <all columns>
FROM idx_FM_BO_NA;

TRUNCATE TABLE idx_FM_BO_NA;

INSERT INTO idx_FM_BO_NA
SELECT * FROM no_dupes;

DROP TABLE no_dupes;
Nem SchlechtIT Supervisor
Top Expert 2009

Commented:
I see you haven't closed out your other question:
  http://www.experts-exchange.com/Database/MySQL/Q_27485356.html

Your above query mainly works, but only if you have only *two* duplicates.  If you have more duplicates, you'll only be getting rid of one of the duplicates.

Here's my query from your other question, which loads all the duplicate IDs into a temporary table (but does *NOT* include the duplicate record with the highest MLS_ID - that's the record you'll have after you delete all the other ones)
 
CREATE TEMPORARY TABLE ids_to_delete
SELECT MLS_ID
FROM idx_FM_BO_NA
WHERE MLS_ID NOT IN (
  SELECT MAX(MLS_ID)
  FROM idx_FM_BO_NA
  GROUP BY STREET_NUMBER, STREET_NAME, STREET_TYPE, STREET_DIRECTION, UNIT_NUMBER
)
;

Open in new window


You can now use a valid DELETE with a join to remove the offending rows:
 
DELETE idx_FM_BO_NA
FROM idx_FM_BO_NA, ids_to_delete
WHERE idx_FM_BO_NA.MLS_ID = ids_to_delete.MLS_ID
;

Open in new window


And when you close the connection, the temp. table will go away.  Nice and clean!
awking00Information Technology Specialist

Commented:
Just remember if you use that method, you will need to re-create any indexes, constraints, etc. that existed on the old table.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial