<div>
Thanks Dan!<br />
That worked! Very quickly!!<br />
<br />
What was wrong with my query? A lot?
</div>


Thanks Dan!
That worked! Very quickly!!

What was wrong with my query? A lot?

<div>
You query looked more like what a programmer would try than what SQL would expect.<br />
<br />
You were JOINining your table with itself on the text field, which I guess was not indexed. <br />
<br />
It created a huge table and I don't really know what MySQL was trying to do with it.
</div>


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.

<div>
<div class="content wysiwyg-content">
Hi,<br />
<br />
I have a table (table1) with 3 columns, id, text, flag.<br />
The table contains tens of thousands of rows with some text.<br />
<br />
Some of these rows are doubles.<br />
I tried this query below to delete these double entries, but it runs for hours and deletes nothing.<br />
<br />
DELETE n1 FROM table1 n1, table1 n2 WHERE n1.id &gt;&nbsp;n2.id AND n1.text = n2.text<br />
<br />
Does anybody have a better (working) and faster query?<br />
<br />
Thanks!
</div>
</div>


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!

Delete double rows in mysql database

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.

MySQL Server

PHP is a widely-used server-side scripting language especially suited for web development, powering tens of millions of sites from Facebook to personal WordPress blogs. PHP is often paired with the MySQL relational database, but includes support for most other mainstream databases. By utilizing different Server APIs, PHP can work on many different web servers as a server-side scripting language.

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.