cleaning up database

[mschultem]

hi,
i have a database with registered users and their demographic information (unique id, name, email ...) which i would like to clean. the problem is that i had no checking for already entered email addresses - thats why i ended up with a lot of double entries.

i can quite filter, based on e-mail addresses through:

SELECT DISTINCT email FROM demography

the problem is that i am not sure how to use the resulting unique email list to flag/delete the rest of the table.

any hints highly appreciated.

thanks
m

[phparion]

there could be different ways. One could be to make a temporary file and copy the distinct emails to it and then use that table e.g

CREATE TABLE temp_emails ...;

INSERT INTO temp_emails(email)
SELECT DISTINCT email FROM old_table;

DROP TABLE old_table;

RENAME TABLE temp_table old_table;

DO BACKUP YOUR ORIGINAL TABLES BEFORE ANY DROPPING OR RENAMING otherwise I am not responsible for any blunder :)

[mschultem]

thanks - i will try this (after backup ;-) )