Delete all Duplicate Rows except for One in MySQL?

Question

Delete all Duplicate Rows except for One in MySQL?

How would I remove all duplicate data from a MySQL Table?

For example, with the following data:

SELECT * FROM names;



+----+--------+

| id | name   |

+----+--------+

| 1  | google |

| 2  | yahoo  |

| 3  | msn    |

| 4  | google |

| 5  | google |

| 6  | yahoo  |

+----+--------+

I would practiceSELECT DISTINCT name FROM names; if it were a SELECT query.

How would I do this with DELETE to only remove duplicates and keep just one record of each?

duplicate sql mysql

4 years ago by Amit8z4mc

2 Answers

Login / Signup to Answer the Question.

Login Signup

**espadacoder11** · Answer 1 · 2021-06-08T12:19:22

If you want to keep the row with the lowest id value:



DELETE FROM NAMES

 WHERE id NOT IN (SELECT  

                    FROM (SELECT MIN(n.id)

                            FROM NAMES n

                        GROUP BY n.name) x)

If you want the id value that is the highest:



DELETE FROM NAMES

 WHERE id NOT IN (SELECT  

                    FROM (SELECT MAX(n.id)

                            FROM NAMES n

                        GROUP BY n.name) x)

The subquery in a subquery is necessary for MySQL, or you'll get a 1093 error.

**RoliMishra** · Answer 2 · 2021-06-09T12:20:22

If you want to keep the row with the lowest id value:

DELETE n1 FROM names n1, names n2 WHERE n1.id > n2.id AND n1.name = n2.name

If you want to keep the row with the highest id value:

DELETE n1 FROM names n1, names n2 WHERE n1.id < n2.id AND n1.name = n2.name

C TUTORIAL

C PROGRAMS

INTERVIEW TESTS

EXECUTE CODE

C++ TUTORIAL

C++ PROGRAMS

INTERVIEW TESTS

EXECUTE CODE

PYTHON TUTORIAL

PYTHON HOW TOS

INTERVIEW TESTS

EXECUTE CODE

JAVA TUTORIAL

JAVA CODE EXAMPLES

SPRING TUTORIAL

MORE IN JAVA

COMPUTER ARCHITECTURE

COMPUTER NETWORK

OPERATING SYSTEM

DBMS & SQL

PL/SQL

MongoDB

EXECUTE SQL

ANDROID DEVELOPMENT

GO LANGUAGE

LINUX

DOCKER

HTML TAGS (A to Z)

CSS REFERENCES

SASS/SCSS

KOTLIN

GAME DEVELOPMENT

PHP

GIT GUIDE

JAVASCRIPT

ADVANCED DSA

Delete all Duplicate Rows except for One in MySQL?

2 Answers

Login / Signup to Answer the Question.