How to find duplicates in 2 columns not 1

Question

I have a MySQL database table with two columns that interest me. Individually, they may have duplicates, but they should never have duplicates of BOTH of them that have the same meaning.

stone_id can have duplicates if each upsharge header upsharge different value, and vice versa. But let's say, for example, stone_id = 412 and upcharge_title = "sapphire" that the combination should be done only once.

This is normal:

 stone_id = 412 upcharge_title = "sapphire" stone_id = 412 upcharge_title = "ruby" 

It is not normal:

 stone_id = 412 upcharge_title = "sapphire" stone_id = 412 upcharge_title = "sapphire" 

Is there a query that will find duplicates in both fields? And if possible, is there a way to establish that my database does not allow this?

I am using MySQL version 4.1.22

Answer 1

You must configure the composite key between the two fields. Each row will require a unique stone_id and upcharge_title value.

As for finding existing duplicates, try this:

 select stone_id, upcharge_title, count(*) from your_table group by stone_id, upcharge_title having count(*) > 1 

Answer 2

It was useful for me to add an unqiue index using "ALTER IGNORE", which removes duplicates and applies unique records that sound the way you would like to do. Thus, the syntax will look like this:

 ALTER IGNORE TABLE `table` ADD UNIQUE INDEX(`id`, `another_id`, `one_more_id`); 

This effectively adds a unique constraint, meaning that you will never have duplicate records, and IGNORE deletes existing duplicates.

You can learn more about eh ALTER IGNORE here: http://mediakey.dk/~cc/mysql-remove-duplicate-entries/

Update: I was informed by @Inquisitive that this may be unsuccessful in versions of MySql> 5.5:

The MySQL> 5.5 command cannot be executed both in the InnoDB table and in Percona due to their InnoDB quick indexing function [ http://bugs.mysql.com/bug.php?id=40344] . In this case, first run set session old_alter_table=1 , and then the above command will work fine

Answer 3

To find duplicates:

 select stone_id, upcharge_title from tablename group by stone_id, upcharge_title having count(*)>1 

To limit this in the future, create a composite unique key in these two areas.

Answer 4

You can find duplicates like this.

 Select stone_id, upcharge_title, count(*) from particulartable group by stone_id, upcharge_title having count(*) > 1 

Answer 5

By the way, a complex unique constraint in the table would prevent this in the first place.

 ALTER TABLE table ADD UNIQUE(stone_id, charge_title) 

(This is valid T-SQL. Not sure about MySQL.)

Answer 6

this SO service helped me, but I also wanted to know how to delete and save one of the rows ... here is a PHP solution to delete duplicate rows and save one (in my case there were only 2 columns, and this is in the function for cleaning duplicate category associations)

 $dupes = $db->query('select *, count(*) as NUM_DUPES from PRODUCT_CATEGORY_PRODUCT group by fkPRODUCT_CATEGORY_ID, fkPRODUCT_ID having count(*) > 1'); if (!is_array($dupes)) return true; foreach ($dupes as $dupe) { $db->query('delete from PRODUCT_CATEGORY_PRODUCT where fkPRODUCT_ID = ' . $dupe['fkPRODUCT_ID'] . ' and fkPRODUCT_CATEGORY_ID = ' . $dupe['fkPRODUCT_CATEGORY_ID'] . ' limit ' . ($dupe['NUM_DUPES'] - 1); } 

(restriction NUM_DUPES - 1) is that it saves one row ...

thanks everyone