To delete duplicate record without primary key or unique key or temp table

Question

I have table like

+----+--------------------+
| email          | name   |
+----+--------------------+
| test@test.com  | test   |
| test2@test.com | test3  |
| test@test.com  | test3  |
| test1@test.com | test1  |
| test2@test.com | test2  |
| test@test.com  | test2  |
+----+--------------------+

I want to delete duplicate email id from table without altering or moving data to temp table?

Why the requirement they should be unique, without being allowed to set a `UNIQUE` index? It would just make sense, and it would be a one liner: `ALTER IGNORE TABLE table ADD UNIQUE(email);` — Wrikken, Nov 28 '14 at 09:59
@juergend : I have want to delete directly from table.I know the temp table way — Bhumi Shah, Nov 28 '14 at 10:01
@BhumiShah it is duplicate. Check out all the answers on that question, it has all possible solutions. — meze, Nov 28 '14 at 11:11
@meze: I have checked all answers of that post. Most of answers are about to use unique key with alter or unique key. But my question is without altering table and creating new temp table — Bhumi Shah, Nov 28 '14 at 12:52

score 0 · Answer 1 · answered Nov 28 '14 at 10:07

0

That is not possible with the restrictions you gave.

Either add a unique index to drop the duplicates automatically or use a temp table to get the unique data and insert it again in the original table.

answered Nov 28 '14 at 10:07

juergen d

201,996
37
293
362

To delete duplicate record without primary key or unique key or temp table

1 Answers1