Finding Duplicates within mySQL

This is how you can find or take a quick look of duplicate data in your table (incase you don’t set that field as unique)

[root@goovle ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 271100
Server version: 5.0.95 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use google_voucher_db
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select v_code,count(v_code) as total from tbl_kd_voucher group by code HAVING ( COUNT(v_code) > 1 );
Empty set (0.06 sec)

the quick and easiest way is protect your field as unique, but in some cases you might find something unusual so just use the query above

mysql> alter table tbl_kd_voucher add unique (v_code);

Advertisements

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 )

w

Connecting to %s