How do I delete duplicate rows in MySQL?

Question

I have some rows in a MySQL database which are duplicates, except for their ID number.

For example, I might have this as two rows:

ID   | wordlist | category
845  | abashed  | confused
1800 | abashed  | confused

I want to find all of them, and only leave one.

Can I use SQL to do this, or do I need to write a program of some sort?

To clarify, I only want to get duplicates in the wordlist section.

if they are referenced in another table that would leave your database incorrect as you won't know which ID was referenced for a particular repeating word — codingbiz, Jul 28 '12 at 16:49

Zane Bien · Accepted Answer · 2012-07-28T17:18:27.287

1

You can do this with an outer join in the DELETE statement:

DELETE a FROM tbl a
LEFT JOIN
(
    SELECT MIN(id) AS id
    FROM tbl
    GROUP BY wordlist
) b ON a.id = b.id
WHERE a.category = '<category here>' AND b.id IS NULL

This will eliminate duplicates (in the wordlist column) for only a specific category and keeps the one with the lowest id.

edited Jul 28 '12 at 17:18

answered Jul 28 '12 at 17:10

Zane Bien

22,685
6
45
57

I do NOT want to delete duplicates in the category section. Just the wordlist section. – Steven Matthews Jul 28 '12 at 17:10
@AndrewAlexander, okay, took out the `category` field in the `GROUP BY`. Try that. – Zane Bien Jul 28 '12 at 17:11
Oh crap, I deleted extra information with this than I wanted to. Wanted to only delete words that were duplicate within a given category. Not your fault, this was close to what I needed, I gave you incorrect information. – Steven Matthews Jul 28 '12 at 17:14
@AndrewAlexander, please tell me you wrapped this in a transaction and were able to `ROLLBACK;`... or have a backup somewhere =(. – Zane Bien Jul 28 '12 at 17:15
1

You shouldn't be doing this on a life data – codingbiz Jul 28 '12 at 17:18
This list is for initial import, so I have the list somewhere. Luckily none of this is collected data. – Steven Matthews Jul 28 '12 at 17:20
What exactly is a JOIN? I'm trying to get better at databases and trying to find reading material about them. – Steven Matthews Jul 28 '12 at 17:37
@AndrewAlexander take a look at [*this*](http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html). – Zane Bien Jul 28 '12 at 17:38

Sourav · Answer 2 · 2012-07-28T17:06:23.373

0

ALTER IGNORE TABLE your_table ADD UNIQUE INDEX idx_wordlist (wordlist );

edited Jul 28 '12 at 17:06

answered Jul 28 '12 at 16:42

Sourav

17,065
35
101
159

"Duplicate entry 'agreeable' for key 'index_wordlist'" – Steven Matthews Jul 28 '12 at 17:05

How do I delete duplicate rows in MySQL?

2 Answers2