CODEWITHSUNDEEP
mysqlsql
Gulmuhammad Akbari
Gulmuhammad Akbari

Reputation: 2036

Mysql delete duplicate records except one has max time

I have a table which has multiple RFID records and each record has a column called time and what I want is deleting duplicate RFID recods except one which has max time.

table name is attendance_images(id,RFID,time,year,month,day) and my query is as below:

DELETE t FROM attendance_images AS t LEFT JOIN (
   SELECT max( t1.time ) AS time
   FROM attendance_images AS t1
   WHERE t1.year=2016
   AND t1.month=8
   AND t1.day=4
   AND t1.time < 120000
   GROUP BY t1.RFID
) keep ON t.time = keep.time
WHERE keep.time IS NULL
AND t.year =2016
AND t.month =8
AND t.day =4
AND t.time < 120000

The query effect is (0 rows deleted. (Query took 0.0034 sec)) but the table has duplicate records according condition.

enter image description here

Please help to fix this issue.

Upvotes: 4

Views: 775

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You need to match on RFID as well:

DELETE t
    FROM attendance_images AS t LEFT JOIN
         (SELECT RFID, max( t1.time ) AS time
          FROM attendance_images AS t1
          WHERE t1.year = 2016 AND t1.month = 8 AND t1.day = 4 AND t1.time < 120000
          GROUP BY t1.RFID
         ) keep
         ON t.time = keep.time AND t.RFID = keep.RFID
WHERE keep.time IS NULL AND
      t.year = 2016 AND t.month = 8 AND t.day = 4 AND t.time < 120000;

Apparently, more than one RFID can have the same maximum time, so joining only on time doesn't work.

EDIT:

You should verify that you actually have data to delete:

select ai.rfid, max(ai.time), min(ai.time), count(*)
from attendance_images ai
where ai.year = 2016 AND ai.month = 8 AND ai.day = 4 AND ai.time < 120000
group by ai.rfid
having min(ai.time) < max(ai.time);

My guess is that this will return no rows, indicating that you have have no such duplicates.

You can change the having clause to having count(*) > 1 to see if you have duplicates that have the same time value.

EDIT II:

The indentation of the values in the query suggest that time is stored as a string, not an integer. You might try replacing the condition:

t.time < 120000

with:

t.time + 0 < 120000

Upvotes: 3

Related Questions