mariadbmariadb-10.4

Remove duplicate records from mysql table

I am trying to delete some duplicate records from mysql table but it was not working.enter image description here

I am taking help of https://www.javatpoint.com/mysql-delete-duplicate-records

If I will try with example database which is given example it was working fine.

But in my table it was not working.

DELETE  S1 FROM employee_attendance AS S1 INNER JOIN employee_attendance AS S2 WHERE S1.DbKey < S2.DbKey AND S1.DivisionDbKey = S2.DivisionDbKey AND S1.Date = S2.Date AND S1.Month = S2.Month AND S1.FinancialYearDbKey = S2.FinancialYearDbKey AND S1.EmployeeDbKey = S2.EmployeeDbKey AND S1.Attendance = S2.Attendance  AND S1.InTime = S2.InTime  AND S1.OutTime = S2.OutTime AND S1.EmployeeDbKey = 6798 AND S1.Month = '05' AND S1.FinancialYearDbKey = 5;

I am getting error

#1205 - Lock wait timeout exceeded; try restarting transaction

I have tried with another example https://www.geeksforgeeks.org/sql-query-to-delete-duplicate-rows/

DELETE FROM employee_attendance WHERE DbKey NOT IN (SELECT MAX(DbKey) FROM employee_attendance WHERE EmployeeDbKey = 6798 AND Month = '05' AND FinancialYearDbKey = '5' GROUP BY DivisionDbKey,Date,Month,FinancialYearDbKey,EmployeeDbKey,Attendance,InTime,OutTime)

I am getting same error.

#1205 - Lock wait timeout exceeded; try restarting transaction

Any suggestion will be appriciated. Thank you.

Solution

  • I personally think this is a bad practice. You should instead make a (empty) duplicate of the table employee_attendance then define a UNIQUE KEY on that new table that will prevent duplicate entries.

    Consider these steps:

    1. Create a duplicate table:
    CREATE TABLE employee_attendance_new LIKE employee_attendance;
    1. Add UNIQUE INDEX - now, this is just a simple example. You can add or reduce columns to the unique index but make sure that you drop the existing unique index first then only you re-create:
    ALTER TABLE employee_attendance_new 
      ADD UNIQUE INDEX unq_idx(EmployeeDbKey, date, InTime, OutTime);
    1. Insert the data into the new table using INSERT IGNORE..:
    INSERT IGNORE INTO employee_attendance_new 
 SELECT * FROM employee_attendance;
    1. Check and compare both table. If you're satisfied with the result, rename those tables:
    RENAME TABLE employee_attendance TO employee_attendance_old;
RENAME TABLE employee_attendance_new TO employee_attendance;
    1. Now you have the new table with no duplicates and the old table for reference or in case there are some data you need from it.

    Fiddle example