I want to delete duplicate rows based on two columns but need to keep 1 row all of them.
Duplicate rows can be more than two rows like,
ID NAME PHONE
-- ---- ----
1 NIL 1234
2 NIL 1234
3 NIL 1234
4 MES 5989
I want to delete any of 2 rows from above 3 and keep 1 row.
mysql> SELECT DISTINCT last_name, first_name -> FROM person_tbl -> ORDER BY last_name; An alternative to the DISTINCT command is to add a GROUP BY clause that names the columns you are selecting. This has the effect of removing duplicates and selecting only the unique combinations of values in the specified columns.
DELETE a
FROM tableA a
LEFT JOIN
(
SELECT MIN(ID) ID, Name, Phone
FROM TableA
GROUP BY Name, Phone
) b ON a.ID = b.ID AND
a.NAme = b.Name AND
a.Phone = b.Phone
WHERE b.ID IS NULL
After you have executed the delete statement, enforce a unique constraint on the column so you cannot insert duplicate records again,
ALTER TABLE TableA ADD CONSTRAINT tb_uq UNIQUE (Name, Phone)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With