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 — mellamokb
– mellamokb, Commented Oct 6, 2011 at 20:06

santiagobasulto · Accepted Answer · 2011-10-06 20:10:48Z

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 6, 2011 at 20:10

santiagobasulto

11.8k13 gold badges70 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

santiagobasulto Over a year ago

Yes, i messed it up with new/old. I Changed the names now.

JellyBelly · Accepted Answer · 2011-10-06 20:07:40Z

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 6, 2011 at 20:07

JellyBelly

2,4312 gold badges21 silver badges42 bronze badges

Comments

Randy · Accepted Answer · 2011-10-06 20:06:41Z

0

do a quick search here for DELETE DUPLICATE ROWS

you'll find a ton of examples.

answered Oct 6, 2011 at 20:06

Randy

16.7k2 gold badges40 silver badges56 bronze badges

Collectives™ on Stack Overflow

mysql, how to delete duplicate data?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related