delete duplicate entires

mrsocks

i have read some of the other posts on this but cant seem to understand the process.

i have a table with 2 colums.
each of the columns will have many duplicate entreis for each colum by itself but i want to remove the entries where both colums are in one record are equal the to same colums in another record.

ex:

col1 | col2

1 | A
1 | B
2 | A
2 | B
1 | A // I would want to remove this one.
2 | B // I would want to remove this one.

how would i do this?

this is kind of what i am thinking:

sel * from table order by col 1 ASC, col 2 ASC

while ($row = mysql_fetch_array($result))
{
$col1 = $row["col1"];
$col2 = $row["col2"];

if (($col1 == $col1["+1"]) && ($col2 == $col2["+1"]))
{delete record, change to '0', whatever }

}

any ideas?
thanks

Sxooter

Nah, do it the easy way, from the sql monitor for your database, issue commands kinda sorta like this (backup first, I'm not responsible for your data...)

select distinct * into newtable from oldtable;
alter table oldtable rename to farkedtable;
alter table newtable rename to oldtable;

recreate indexes as needed.

Note that if you have triggers, rules, etc... on that table, you can do it this way:

select distinct into newtable from oldtable;
delete from oldtable;
insert into newtable (select from oldtable);

I'm not sure that works on MySQL, but it does on pgsql.

Sxooter

Oh, and once you're done deleting those dupes, add a constraint to your database:

create unique index indexontable on table (field1, field2);

So you can't get anymore dupes.