Mysql Query to delete duplicates

Question

I have duplicate results like below where some column may have data and may not

| contact_info | icon | id  | title         | lastmodified_by  |
+--------------+------+-----+---------------+------------------+
|          169 |  305 | 123 | Whakarewarewa | 2011100400305262 |
|         NULL | NULL | 850 | Whakarewarewa | NULL             |
+--------------+------+-----+---------------+----------------



| contact_info | icon | id  | title         | lastmodified_by  |
+--------------+------+-----+---------------+------------------+
|         NULL | NULL | 123 | Paris         | NULL             |
|         NULL | NULL | 850 | Paris         | NULL             |
+--------------+------+-----+---------------+----------------

I want to delete record having less Data and if the all the Field values are exact same then delete any row. There are thousand records like this.

It is obvious that there would be multiple records else i wouldn't have asked this question here on stackoverflow. — saun jean
– saun jean, Commented Jun 5, 2012 at 8:29
This question was askwed trillions of time, here, here, here, here, here... — lorenzo-s
– lorenzo-s, Commented Jun 5, 2012 at 8:44
This question is different from all those. Here there are 3 cases 1) Columns having exactly same data 2) One column is having data and other not . 3) Cannot delete all min ids or max ids because data can be either in max id or min id — saun jean
– saun jean, Commented Jun 5, 2012 at 8:48
@lorenzo-s this links describe how to remove duplicates, but does not consider NULL values - 'record having less Data'. — Devart
– Devart, Commented Jun 5, 2012 at 8:49
@saunjean Ok, but at least you could have tried something starting from these answer, and then posting your code together with question. — lorenzo-s
– lorenzo-s, Commented Jun 5, 2012 at 8:49

Devart · Accepted Answer · 2012-06-05 10:29:38Z

3

Try this two-step solution:

Run this query to vew all duplicates - record having less Data -

SELECT t1.* FROM table t1
  JOIN (
    SELECT
      title,
      MIN(IF(contact_info IS NULL, 0, 1) + IF(contact_info IS NULL, 0, 1) + IF(lastmodified_by IS NULL, 0, 1)) min_value_data,
      MAX(IF(contact_info IS NULL, 0, 1) + IF(contact_info IS NULL, 0, 1) + IF(lastmodified_by IS NULL, 0, 1)) max_value_data
    FROM table GROUP BY title HAVING min_value_data <> max_value_data
  ) t2
  ON t1.title = t2.title AND IF(t1.contact_info IS NULL, 0, 1) + IF(t1.contact_info IS NULL, 0, 1) + IF(t1.lastmodified_by IS NULL, 0, 1) <> t2.max_value_data

Rewrite it to DELETE statement and execute.

Then run this query to remove all duplicates except min ID:

DELETE t1 FROM table t1
  JOIN (SELECT MIN(id) id, title FROM table GROUP BY title) t2
    ON t1.id <> t2.id AND t1.title = t2.title;

edited Jun 5, 2012 at 10:29

answered Jun 5, 2012 at 8:33

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

saun jean Over a year ago

What if any record with min id has no data but max id has data in it. It will delete id with max id and keep id with min id???

Devart Over a year ago

Yes, you are right. I read your edited question; and the query should be rewrited.

saun jean Over a year ago

It is giving this "ERROR 1052 (23000): Column 'title' in field list is ambiguous" . Then i thought to put t1.title in starting of the query but then it gives "ERROR 1054 (42S22): Unknown column 't1.value_data' in 'on clause'"

Devart Over a year ago

Add table alias (the field should be t1.title).

saun jean Over a year ago

I Tried the same thing before posting the comment and the error is still the same "ERROR 1054 (42S22): Unknown column 't1.value_data' in 'on clause'"

|

Ramon Fincken · Accepted Answer · 2012-06-05 08:55:35Z

1

Use this to select duplicates, feel free to alter this to a delete statement:

SELECT * FROM `test`,
(SELECT title, count( title ) AS ttl
FROM `test`
GROUP BY title
HAVING ttl >1) AS sub

WHERE test.title = sub.title
AND contact_info IS NULL AND lastmodified_by IS NULL

answered Jun 5, 2012 at 8:55

Ramon Fincken

3011 gold badge5 silver badges11 bronze badges

4 Comments

saun jean Over a year ago

This query fails in the case Where title=Paris . It lists both the Paris in the Result. This is the case where both results are exact duplicates including all fields

Ramon Fincken Over a year ago

Correct, I did not see your Paris example. How about this: Showing rows 0 - 1 (2 total, Query took 0.0007 sec) SELECT * FROM test AS main, ( SELECT title, count( title ) AS ttl FROM test GROUP BY title HAVING ttl >1 ) AS sub WHERE main.title = sub.title AND main.contact_info IS NULL AND main.lastmodified_by IS NULL GROUP BY main.contact_info, main.icon, main.title, main.lastmodified_by

saun jean Over a year ago

This query is almost correct except the part that Table in which rows to be modified or deleted cannot be referenced in Subquery.so i cannot delete using this query

Ramon Fincken Over a year ago

See my new answer below. using more queries.

Ramon Fincken · Accepted Answer · 2012-06-05 11:14:35Z

0

Main table = tes1

Create temp

CREATE TEMPORARY TABLE my_temp ( id INT(20) NOT NULL ) ENGINE=MEMORY;

Fill with id's to remove

INSERT INTO my_temp (id) SELECT id FROM tes1 AS main, ( SELECT title, count( title ) AS ttl FROM tes1 GROUP BY title HAVING ttl >1 ) AS sub WHERE main.title = sub.title AND main.contact_info IS NULL AND main.lastmodified_by IS NULL GROUP BY main.contact_info, main.icon, main.title, main.lastmodified_by;

Delete!

DELETE FROM tes1 WHERE id IN (select id from my_temp);

Cleanup, note: do we really need this?

DROP TABLE my_temp;

answered Jun 5, 2012 at 11:14

Ramon Fincken

3011 gold badge5 silver badges11 bronze badges

Collectives™ on Stack Overflow

Mysql Query to delete duplicates

3 Answers 3

6 Comments

4 Comments

Main table = tes1

Create temp

Fill with id's to remove

Delete!

Cleanup, note: do we really need this?

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

4 Comments

Main table = tes1

Create temp

Fill with id's to remove

Delete!

Cleanup, note: do we really need this?

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related