Delete duplicate fingerprint

[Jassim Rahma]

Hi, I would like to ask how can I delete duplicate records in MySQL from my fingerprint table. I need to delete any record if found the same created_date AND created_time AND employee_number Thanks, Jassim

Technology News @ www.JassimRahma.com

[Chris Quinn]

Don't use MySQL but in SQl server you would do this:

WITH CTE_fingerprint
AS (
SELECT
employee_number
,created_date
,created_time
,ROW_NUMBER() OVER (
PARTITION BY
employee_number
,created_date
,created_time
ORDER BY
employee_number
,created_date
,created_time
) AS RowNumber
FROM [fingerprint]
)

DELETE
FROM CTE_fingerprint
WHERE RowNumber > 1;

========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================

[Bernhard Hiller]

Not sure with self-joins in MySQL. This query should show you all the duplicates (assuming ID is the identifier in the table):

SELECT *
FROM fingerprint t1 inner join fingerprint t2 on
t1.created_date=t2.created_date and t1.created_time = t2.created_time and t1.employee_number=t2.employee_number
Where t1.ID
Next, change SELECT * to SELECT t1.ID to get the ID values of the duplicates only, then do a delete query with a subquery:

DELETE
FROM fingerprint
WHERE fingerprint.ID IN
(
SELECT t1.ID
FROM ... (see query above)
)