mysql duplicate row delete code example
Example 1: how to delete all duplicate items in mysql
DELETE FROM FriendsData WHERE fID
NOT IN ( SELECT fID FROM FriendsData
GROUP BY UserID, FriendsUserID, IsSpecial, CreatedBy)
Example 2: mysql delete duplicate rows
DELETE FROM table_name
WHERE
id IN (
SELECT
id
FROM (
SELECT
id,
ROW_NUMBER() OVER (
PARTITION BY field_1
ORDER BY field_1) AS row_num
FROM
table_name
) t
WHERE row_num > 1
);
Example 3: mysql delete duplicate rows except one
DELETE FROM NAMES
WHERE id NOT IN (SELECT *
FROM (SELECT MIN(n.id)
FROM NAMES n
GROUP BY n.name) x)