• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 550
  • Last Modified:

Delete double rows in mysql database

Hi,

I have a table (table1) with 3 columns, id, text, flag.
The table contains tens of thousands of rows with some text.

Some of these rows are doubles.
I tried this query below to delete these double entries, but it runs for hours and deletes nothing.

DELETE n1 FROM table1 n1, table1 n2 WHERE n1.id > n2.id AND n1.text = n2.text

Does anybody have a better (working) and faster query?

Thanks!
0
peps03
Asked:
peps03
  • 2
  • 2
1 Solution
 
Dan CraciunIT ConsultantCommented:
Create an empty table2.

INSERT INTO table2 (id, text, flag) (SELECT id, text, flag FROM table1 GROUP BY table1.text)

HTH,
Dan
0
 
peps03Author Commented:
Thanks Dan!
That worked! Very quickly!!

What was wrong with my query? A lot?
0
 
Dan CraciunIT ConsultantCommented:
You query looked more like what a programmer would try than what SQL would expect.

You were JOINining your table with itself on the text field, which I guess was not indexed.

It created a huge table and I don't really know what MySQL was trying to do with it.
0
 
peps03Author Commented:
Ok, thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now