Home > Coding, MySQL > Cleaning tables of duplicates

Cleaning tables of duplicates

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:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: