CODEWITHSUNDEEP
phpmysql
Patrioticcow
Patrioticcow

Reputation: 27038

mysql, how to delete duplicate data?

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

Upvotes: 1

Views: 248

Answers (3)

santiagobasulto
santiagobasulto

Reputation: 11686

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.

Upvotes: 2

JellyBelly
JellyBelly

Reputation: 2431

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.

Upvotes: 1

Randy
Randy

Reputation: 16677

do a quick search here for DELETE DUPLICATE ROWS

you'll find a ton of examples.

Upvotes: 0

Related Questions