Delete duplicate records in MySQL

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

pda4meAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

awking00Commented:
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;
0
nemws1Database AdministratorCommented:
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!
0
awking00Commented:
Just remember if you use that method, you will need to re-create any indexes, constraints, etc. that existed on the old table.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.