delete duplicate rows that have blob text / mediumtext mysql

Question

I have seen lots of posts on deleting rows using sql commands but i need to filter out rows which have mediumtext.

I keep getting an error Error Code: 1170. BLOB/TEXT column used in key specification without a key length from solution such as:

ALTER IGNORE TABLE foobar ADD UNIQUE (title, SID)

My table is simple, i need to check for duplicates in mytext, id is unique and they are AUTO_INCREMENT.
As a note, the table has about a million rows, and all attempts keep timing out. I would need a solution that performs actions in batches such as WHERE id>0 AND id<100
Also I am using MySQL Workbench on amazons RDS

From a table like this

+---+-----+-----+------+-------+
|id |fname|lname|mytext|morevar|
|---|-----|-----|------|-------|
| 1 | joe | min | abc  | 123   |
| 2 | joe | min | abc  | 123   |
| 3 | mar | kam | def  | 789   |
| 4 | kel | smi | ghi  | 456   |
+------------------------------+

I would like to end up with a table like this

+---+-----+-----+------+-------+
|id |fname|lname|mytext|morevar|
|---|-----|-----|------|-------|
| 1 | joe | min | abc  | 123   |
| 3 | mar | kam | def  | 789   |
| 4 | kel | smi | ghi  | 456   |
+------------------------------+

update forgot to mention this is on amazon RDS using mysql workbench
my table is very large and i keep getting an error Error Code: 1205. Lock wait timeout exceeded from this sql command:

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

Also, if anyone else is having issues with MySQL workbench timing out the fix is
Go to Preferences -> SQL Editor and set to a bigger value this parameter: DBMS connection read time out (in seconds)

@dianuj its a text type: http://www.sqlinfo.net/mysql/mysql_data_types.php — t q, Jun 27 '13 at 18:24
Check this answer from a possible duplicate: http://stackoverflow.com/a/2630478/1587345 — Alejandro Colorado, Jun 27 '13 at 18:55
@AlejandroColorado this statement didnt work for me, it did not filter out the duplicates `CREATE TEMPORARY TABLE bad_temp AS SELECT DISTINCT * FROM bad_table` — t q, Jun 27 '13 at 19:01
@AlejandroColorado also i am getting errors `Error Code: 1064.` and `Error Code: 1241` from `INSERT INTO bad_temp(id,name) SELECT DISTINCT id,name FROM bad_table;` — t q, Jun 27 '13 at 19:27
@AlejandroColorado yes, i would like to get rid of rows that have duplicate `mytext` columns, the id's are unique since they are on `AUTO_INCREMENT` — t q, Jun 28 '13 at 13:32

Alejandro Colorado · Accepted Answer · 2013-06-28T13:57:23.023

2

OPTION #1: Delete all duplicates records leaving one of each (e.g. the one with max(id))

DELETE
FROM yourTable
WHERE id NOT IN
(
   SELECT MAX(id)
   FROM yourTable
   GROUP BY mytext
)

You could prefer using min(id).

Depending on the engine used, this won't work and, as it did, give you the Error Code: 1093. You can't specify target table 'yourTable' for update in FROM clause. Why? Because deleting one record may cause something to happen which made the WHERE condition FALSE, i.e. max(id) changes the value.

In this case, you could try using another subquery as a temporary table:

DELETE
FROM yourTable
WHERE id NOT IN
(
    SELECT MAXID FROM
    (
        SELECT MAX(id) as MAXID
        FROM yourTable
        GROUP BY mytext
    ) as temp_table
)

OPTION #2: Use a temporary table like in this example or:

First, create a temp table with the max ids:

   SELECT MAX(id) AS MAXID
   INTO tmpTable
   FROM yourTable
   GROUP BY mytext;

Then execute the delete:

DELETE
FROM yourTable
WHERE id NOT IN
(
    SELECT MAXID FROM tmpTable
);

edited Jun 28 '13 at 13:57

answered Jun 28 '13 at 08:57

Alejandro Colorado

6,034
2
28
39

from option 1 i get an error `Error Code: 1093. You can't specify target table 'yourTable' for update in FROM clause` – t q Jun 28 '13 at 13:20
Obvously, You must change `yourTable` to the name of **your table**. – Alejandro Colorado Jun 28 '13 at 13:26
yes, that is what i did. I just used names consistent with your answer. – t q Jun 28 '13 at 13:28
also from option 2, the example has `(1,'Things Fall Apart'), (2,'The Famished Road'), (3,'Thirteen cents'), (3,'Thirteen cents');` rows with identical `id`'s, mine have unique id's and all we're looking for is duplicate `mytext` values – t q Jun 28 '13 at 13:47
thank you! the second answer from option1 looks like its working – t q Jun 28 '13 at 14:10
the second option gives an `error 1327` - INSERT ... SELECT http://dev.mysql.com/doc/refman/5.1/en/insert-select.html – t q Jul 10 '13 at 19:21

M Khalid Junaid · Answer 2 · 2013-06-27T18:48:08.797

0

How about this it will delete all the duplicate records from the table

 DELETE t1 FROM foobar t1 , foobar t2 WHERE  t1 .mytext= t2.mytext

edited Jun 27 '13 at 18:48

answered Jun 27 '13 at 18:32

M Khalid Junaid

63,861
10
90
118

getting an error: `Error Code: 1093. You can't specify target table 'mytable' for update in FROM clause` – t q Jun 27 '13 at 18:36
@CBroe haha, yes `CREATE TABLE t1 AS (SELECT * FROM t2);` – t q Jun 28 '13 at 13:12

delete duplicate rows that have blob text / mediumtext mysql

2 Answers2

Linked