Question: I have a `MySQL` table like:

```
ID, Col1, Col2, Col3, Col4, etc...

```

ID is a **`primary key`** and has been working since the table's creation.

What I want to do is delete all but one records where all the other columns are identical.

Answer: 
Without nested selects or temporary tables. 

```
DELETE  t1
FROM    table_name t1, table_name t2
WHERE   
            (t1.Col1 = t2.Col1 OR t1.Col1 IS NULL AND t2.Col1 IS NULL)
        AND (t1.Col2 = t2.Col2 OR t1.Col2 IS NULL AND t2.Col2 IS NULL)
        AND (t1.Col3 = t2.Col3 OR t1.Col3 IS NULL AND t2.Col3 IS NULL)
        AND (t1.Col4 = t2.Col4 OR t1.Col4 IS NULL AND t2.Col4 IS NULL)
        ...
        AND t1.ID < t2.ID;

```
you can also do this

```
Create table new_table{id, col1,col2,col3}

insert into new_table values(select distinct * from old_table)

drop table old_table

```