How to delete duplicate records but except one for Int?

Question

Under my Table records for MySQL, i have these:

SELECT * FROM dbo.online;
+-------+
| Id    |
+-------+
| 10128 |
| 10240 |
|  6576 |
|    32 |
| 10240 |
| 10128 |
| 10128 |
| 12352 |
+-------+
8 rows in set (0.00 sec)

How to make it to:

 SELECT * FROM dbo.online;
+-------+
| Id    |
+-------+
| 10128 |
| 10240 |
|  6576 |
|    32 |
| 12352 |
+-------+
8 rows in set (0.00 sec)

In other words, I want to do is, using DELETE command instead of SELECT * FROM dbo.online GROUP BY id.. So, any idea how?

possible duplicate of [how to delete duplicates on mysql table?](http://stackoverflow.com/questions/2630440/how-to-delete-duplicates-on-mysql-table) — Emil Vikström, Aug 07 '13 at 11:54

Robert · Answer 1 · 2013-08-07T12:09:15.097

2

Copy data to back up table with distinct, that steop eliminates duplicates

create table backUp_online as
SELECT distinct * 
FROM online;

Clear source table

truncate table online

Copy data from back up to source table without duplicates

   insert into online
   select *
   from backUp_online

edited Aug 07 '13 at 12:09

answered Aug 07 '13 at 11:59

Robert

25,425
8
67
81

. . Close, but MySQL uses `create table as` rather than `select into`. – Gordon Linoff Aug 07 '13 at 12:08

score 1 · Answer 2 · edited May 23 '17 at 10:31

1

There is a trick in MySQL:

ALTER IGNORE TABLE `dbo`.`online` ADD UNIQUE KEY `ukId`(`Id`)

This can also be useful.

edited May 23 '17 at 10:31

Community

1
1

answered Aug 07 '13 at 11:54

Alma Do

37,009
9
76
105

score 0 · Answer 3 · answered Aug 07 '13 at 12:01

0

Simplest query to do the same.

DELETE n1 FROM online n1, online n2 WHERE n1.id < n2.id AND n1.name = n2.name

answered Aug 07 '13 at 12:01

Shaikh Farooque

2,620
1
19
33

How to delete duplicate records but except one for Int?

3 Answers3