Is there an easy way to delete DUPLICATES?

nicdp

Is there an easy way to delete duplicate rows?

This is my DB:
partyround(
id int(4) DEFAULT '0' NOT NULL auto_increment,
id_cat int(4),
cod_iscritto varchar(30),
PRIMARY KEY (id)
);

nemonoman

you've got an auto increment id, so you don't have any "true" duplicates.

What do you consider duplicated, exactly?

id_cat int(4)?

cod_iscritto varchar(30)?

Or BOTH columns?

nicdp

I mean when id_cat and cod_iscritto are the same in different rows.

EXAMPLE:
id=1 id_cat=20 cod_iscritto=XXX
id=2 id_cat=20 cod_iscritto=ABC
id=3 id_cat=55 cod_iscritto=ABC
id=4 id_cat=20 cod_iscritto=XXX

ROW 1 and 4 are the duplicate rows.

[deleted]

Read this:

http://www.yapf.net/faq.php?cmd=viewitem&itemid=48

nicdp

Originally posted by vincente
Read this:
http://www.yapf.net/faq.php?cmd=viewitem&itemid=48

Sorry but that's not my problem if you read my problem: there is a couple of fields which must be different, so it is not possible to solve it just choosing a unique key.

[deleted]

"Sorry but that's not my problem if"

The article doesn't mention it because I thought it would be too obvious to have to explain, but unique keys can span more than just one column (what would be the point of having them if they coulnd't)

nicdp

How should the code of my table be? Something like this?

partyround(
id int(4) DEFAULT '0' NOT NULL auto_increment,
id_cat int(4),
cod_iscritto varchar(30),
PRIMARY KEY (id),
UNIQUE(id_cat,cod_iscritto)
);

[deleted]

That seems about right.

for the exact syntax, check the mysql syntax documentation:
http://www.mysql.com/doc/en/CREATE_TABLE.html
http://www.mysql.com/doc/en/ALTER_TABLE.html

nicdp

Thanks/Bedankt... I was just looking at it!

[deleted]

You're welcome /graag gedaan

nicdp

COOL!!! IT WORKS NOW WITH THIS TABLE, NOW I ONLY HAVE TO IMPORT DATA IN THE NEW TABLE!! :=) (Het is gelukt! Doei!)

partyround(
id int(4) DEFAULT '0' NOT NULL auto_increment,
id_cat int(4),
cod_iscritto varchar(30),
PRIMARY KEY (id),
UNIQUE(id_cat,cod_iscritto)
);