Set Unique indices but duplicate rows

[optikalefx]

So i'm taking over a big database for a client.  I really need to make a unique index on the table, but I can't because there are already tons of duplicates from bad design in the past.

 

What is a quick way to kill all these duplicates so I can set the unique index?

[donnan]

Firstly BACKUP the original db.

 

I would create a new table with an id field with Auto Increment set then use something like this: 

 

SELECT distinct email FROM originaltable WHERE id > 0

 

Get the results of that query and then paste them into this.

 

INSERT INTO newtable SET email = value1, name = value2.....then any other fields.

 

Hope this helps.

[kickstart]

Hi

 

Does it need to be a unique index? Is there not already one? Does the unique index need to be meaningful or would a sequence number be OK?

 

All the best

 

Keith

[optikalefx]

It does not have a unique index already, but the goal of this is to add a unique index. That way these duplicates can never happen again.

I want to do this to another table as well that needs a unique index based on 2 fields.

 

[kickstart]

Hi

 

For a unique key I would just add an INT auto increment primary key to the table. If you add this it should auto populate.

 

All the best

 

Keith

[optikalefx]

Well a primary key already exists, this is another field.  Basically the user_id is the primary unique key.  But there are duplicate usernames.  and right now people have duplicate usernames.  I want to somehow purge all duplicates.

[Pikachu2000]

I wrote this a while back to check a single field in a table for duplicate entries. You should be able to use it or modify it to at least build the list of duplicates.

 

<?php
function mysqli_check_duplicates($db_conn, $table, $field, $flag = 1) {
    $field = $field;
    $table = $table;
    require_once("$db_conn");
    $output = '';
    $query = "SELECT `{$field}`, COUNT(`{$field}`) AS c FROM `{$table}` GROUP BY `{$field}` ORDER BY c DESC";
    $result = mysqli_query( $dbc, $query );
    $output = "<table>";

    while( $array = mysqli_fetch_assoc($result) ) {
        if( $array['c'] > $flag ) {
            $bg != "CCCCCC" ? $bg = "CCCCCC" : $bg = "FFFFFF";
            $count = $array['c'];
            $output .=  "<tr bgcolor=\"$bg\"><td>Value:</td><td>" . $array[$field] . "</td><td>Entries:</td><td>" . $count . "</td></tr>";
        }
    }
    $output .= "</table>";
    return $output;
}

// mysqli_check_duplicates('db_conn_with_path', 'table_name', 'field_name', $flag); // $flag - 0 = show all rows, 1 = (default) only show duplicates
?>

[optikalefx]

Thanks! that gave me the list. Now I think I can write a script to use this data to determine if a duplicate should be delete or not, and then i can finally unique the table.

woot!