How to delete duplicates on a MySQL table by date and id?

Question

I have a table with results that come from an app. Some of the results are duplicate and I can see which "survey_question_id" has duplicate answers.

With this query I can see how many duplicates I have for each "survey_question_id":

    SELECT `id`,`survey_question_id`,
   `question`,`date`, COUNT(*) 
   FROM answers 
   GROUP BY `survey_question_id`, `date` 
   HAVING COUNT(*) > 1 and `date` > '2015-10-15'

Table Results:

How can I delete duplicates?

You want to delete all of those answers or want to keep only one of duplicate answers for each question? — Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ, Dec 31 '15 at 14:28
@TareqMahmood I want to keep only one of duplicate answers for each question. — pmcmf, Dec 31 '15 at 14:39
@JayBlanchard it's not the same because to be duplicate it must have the same survey_question_id, question and date. — pmcmf, Dec 31 '15 at 14:41
No, not true. Deleting duplicates from MySQL is a general concept and typically not specifically related to column names and / or data. — Jay Blanchard, Dec 31 '15 at 14:45
Possibly you could use that solution if you have a field with only date ( not included time) — Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ, Dec 31 '15 at 14:46
@TareqMahmood date and time are on the same field. Maybe in the future we will separate them into different fields. — pmcmf, Dec 31 '15 at 15:14
There was an answer that solved my problem but it was deleted. The user was PhizerFernandes. Is it possible to recover it? — pmcmf, Dec 31 '15 at 15:48
@pmcmf I suggest to create a unique index for resolving this issue — devpro, Dec 31 '15 at 17:24

score 2 · Answer 1 · edited May 23 '17 at 11:44

2

You can

create table your_table2 as select distinct * from original_table and then just
drop original_table with some cascade constraints and
alter your_table2, rename it and add back all constraints or just delete all data from original_table and do just insert into original_table select * from your_table2

I think that this is similar to your problem.

edited May 23 '17 at 11:44

Community

1
1

answered Dec 31 '15 at 15:00

Ivanka Eldé

228
1
2
12

Nice approach to save data in tmp table ist – devpro Dec 31 '15 at 16:54

devpro · Answer 2 · 2016-01-01T06:01:09.903

1

For Duplicate records deletion, you can use this:

IF you want to keep LOWEST id than you can use this:

DELETE n1 FROM answers n1, answers n2 WHERE n1.id > n2.id 
AND n1.id <> n2.id
AND n1.survey_question_id = n2.survey_question_id 
AND n1.question = n2.question 
AND n1.date = n2.date

IF you want to keep HIGEST id than you can use this:

DELETE n1 FROM answers n1, answers n2 WHERE n1.id < n2.id 
AND n1.id <> n2.id
AND n1.survey_question_id = n2.survey_question_id 
AND n1.question = n2.question 
AND n1.date = n2.date

Add UNIQUE INDEX if you think this can be happened again in future.

ALTER IGNORE TABLE answers   
ADD UNIQUE INDEX (id,survey_question_id,
   question,date);

Side Note: please make sure you have a copy of this table before executing any kind of bulk Deletion.

edited Jan 01 '16 at 06:01

answered Dec 31 '15 at 17:02

devpro

16,184
3
27
38

I tried this but gives the result: MySQL returned an empty result set (i.e. zero rows). – pmcmf Dec 31 '15 at 21:41
@pmcmf how many column do u have in this table 4? – devpro Jan 01 '16 at 03:11
@pmcmf: i have updated my answer – devpro Jan 01 '16 at 06:01
yours is similar to this one: DELETE a1 FROM answers a1, answers a2 WHERE a1.id> a2.id AND a1.survey_question_id = a2.survey_question_id AND a1.question = a2.question AND a1.date = a2.date AND a1.date > '2015-10-15' – pmcmf Jan 02 '16 at 21:44
No @pmcmf > < diff only – devpro Jan 02 '16 at 21:48

score 0 · Answer 3 · answered Dec 31 '15 at 15:13

0

Try this:

DELETE FROM answers
WHERE date > '2015-10-15'
AND id NOT IN 
  (SELECT id
      FROM (SELECT MIN(id) AS id  FROM answers
            WHERE date > '2015-10-15'
            GROUP BY survey_question_id) x
  )

answered Dec 31 '15 at 15:13

Plebios

835
1
7
17

1

Its very risky query – devpro Dec 31 '15 at 16:54
This would delete a bunch of good data while leaving duplicates in the system. Based on the data in the screenshot, the 2 duplicates of ID 141450 would be left intact, and all other data (in the screenshot) would be destroyed. – EvilBob22 Dec 31 '15 at 16:56
Sorry but this one does not work. Thanks anyway – pmcmf Dec 31 '15 at 21:20

How to delete duplicates on a MySQL table by date and id?

3 Answers3