mysql, how to delete duplicate data?

Question

i have a table with some duplicate values and i want to remove them:

table1:

id   |   access       |   num
12       1144712030       101
13       1144712030       101
14       1154512035       102
15       1154512035       102

i would like to remove the duplicates so i will have left:

id   |   access       |   num
12       1144712030       101
14       1154512035       102

any idea how to do this in a mysql command?

thanks

possible duplicate of [How to remove duplicates from table using SQL query](http://stackoverflow.com/questions/7676110/how-to-remove-duplicates-from-table-using-sql-query) — mellamokb, Oct 06 '11 at 20:06

score 2 · Accepted Answer · answered Oct 06 '11 at 20:10

2

The simpler solution i think would be:

CREATE TABLE new_table as SELECT id,DISTINCT access,num FROM original_table
TRUNCATE TABLE original_table
INSERT INTO original_table SELECT * FROM new_table
DROP TABLE new_table;

Note:

I think some kind of cursor could be used, and maybe a temporary table. But you should be really careful.

answered Oct 06 '11 at 20:10

santiagobasulto

11,320
11
64
88

Yes, i messed it up with new/old. I Changed the names now. – santiagobasulto Oct 06 '11 at 20:13

score 1 · Answer 2 · answered Oct 06 '11 at 20:07

1

if your table called foo, rename in foo_old, re-create table foo as a structure identical to foo_old. Make a query with the DISTINCT operator obtained and the results reported on Table foo_old enter them in foo.

answered Oct 06 '11 at 20:07

JellyBelly

2,451
2
21
41

score 0 · Answer 3 · answered Oct 06 '11 at 20:06

0

do a quick search here for DELETE DUPLICATE ROWS

you'll find a ton of examples.

answered Oct 06 '11 at 20:06

Randy

16,480
1
37
55

mysql, how to delete duplicate data?

3 Answers3