Delete Duplicate records and keep one in MYSQL version 5.7 ( Table with out primary key)

user_14626021 0
Viewed 38

We have some duplicate entries in our Items Table and trying to delete them but need one out of them

Table: Items (No Primary Key

ItemNumber,lastModifiedDate
10056,'2020-10-19'
10056,'2020-10-19'
10057,'2020-10-19'
10057,'2020-10-20'

Expected Output:

ItemNumber,lastModifiedDate
10056,'2020-10-19'
10057,'2020-10-20'

I tried below :

delete from Items where (ItemNumber,LastModifiedDate) not in
(
SELECT
ItemNumber,max(LastModifiedDate) LastModifiedDate
FROM
(select * from Items ) Items
GROUP BY
ItemNumber
);

We can do it in Mysql V8 using ROW_NUMBER() windows Function, but that feature is not available in 5.7, and i can't upgrade the DB now.

Thanks in Advance

duplicates mysql
1 Answers

Your problem is actually tricky because your duplicate records are really identical in every way. One approach here is to filter off the duplicates in a temporary table. Then truncate your current table and populate it using the filtered data.

CREATE TEMPORARY TABLE ItemsTemp AS (
    SELECT ItemNumber, MAX(lastModifiedDate) AS lastModifiedDate
    FROM Items
    GROUP BY ItemNumber
)

TRUNCATE TABLE Items;  -- remove all data in Items

-- repopulate Items using non duplicate data
INSERT INTO Items (ItemNumber, lastModifiedDate)
SELECT ItemNumber, lastModifiedDate
FROM ItemsTemp;

DROP TABLE ItemsTemp;  -- drop the temporary table
user_18632290
Related