CODEWITHSUNDEEP
mysql
Rocco The Taco
Rocco The Taco

Reputation: 3797

Delete duplicates from `MySQL` and keep single record

I am using the following query to find duplicate records and have verified it runs correctly. 

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;

How do I alter this query to delete duplicates so that there is only one instance of the record? I don't worried which record(s) get deleted, but I need one of them to stay.

Upvotes: 0

Views: 2984

Answers (3)

nikos
nikos

Reputation: 11

The following MySQL commands will create a temporary table and populate it with all columns GROUPED by one column name (the column that has duplicates) and order them by the primary key ascending. The second command creates a real table from the temporary table. The third command drops the table that is being used and finally the last command renames the second temporary table to the current being used table name.

Thats a really fast solution.
Here are the four commands:

  1. CREATE TEMPORARY TABLE videos_temp AS SELECT * FROM videos GROUP by title ORDER BY videoid ASC;
  2. CREATE TABLE videos_temp2 AS SELECT * FROM videos_temp;
  3. DROP TABLE videos;
  4. ALTER TABLE videos_temp2 RENAME videos;

Upvotes: 0

B T
B T

Reputation: 61023

I think this answer is correct:

delete from table1 USING table1, table1 as vtable 
WHERE table1.ID<vtable.ID AND table1.field_name=vtable.field_name

The only difference between this one and paulsm4's answer that the ids are compared using less-than rather than not-equals. That way, later are not compared with earlier records (which means that one and only one of the identical records will be kept). I tested this out with my own data that I needed this for, and it worked.

Upvotes: 2

paulsm4
paulsm4

Reputation: 121881

Here's a simple solution:

http://www.cyberciti.biz/faq/howto-removing-eliminating-duplicates-from-a-mysql-table/

delete from table1 USING table1, table1 as vtable 
WHERE (NOT table1.ID=vtable.ID) AND (table1.field_name=vtable.field_name)
  1. Here you tell mysql that there is a table1.
  2. Then you tell it that you will use table1 and a virtual table with the values of table1.
  3. This will let mysql not compare a record with itself!
  4. Here you tell it that there shouldn’t be records with the same field_name.

Upvotes: 0

Related Questions