Archive

Posts Tagged ‘mysql’

Cleaning tables of duplicates

August 15, 2012 Leave a comment

Cleaning a MySQL database called presschoice that has combined duplicate values candidate_id and press_id columns. The table only has 3 columns, presschoice_id (primary key, auto_increment), candidate_id (int), presschoice_id (int).

# first checking how many duplicates you have

SELECT * FROM presschoice p;
select
candidate_id,
press_id,
count(*) as n

from presschoice
group by candidate_id,press_id
having n > 1

# creating a temp table to store the distinct values
create temporary table presschoice_clean2 as SELECT distinct candidate_id,press_id FROM presschoice p;

# deleting the orginal values from the table. Make sure you have a backup!
delete from presschoice;

# add the new “clean” values.
insert into presschoice(candidate_id, press_id) select candidate_id,press_id from presschoice_clean2;

# also deleting any rogue data
delete from presschoice where candidate_id=0;

Looking into creating a Trigger so the duplicate values don’t go in. That maybe in next post!

Advertisements
Categories: Coding, MySQL Tags: