PHP - Deleting duplicate values with same ref

Question

I've the following MySQL Table called store

id ref item_no supplier
1  10    x1      usa
2  10    x1      usa
3  11    x1      china
4  12    x2      uk
5  12    x3      uk
6  13    x3      uk
7  13    x3      uk

Now What i'm excepting the output to be is as follows :

id ref item_no supplier
1  10    x1      usa
3  11    x1      china
4  12    x2      uk
5  12    x3      uk
6  13    x3      uk

As you can see item_no x1 and x3 have same ref and supplier source, so what I want is to delete the duplicate record in-order to keep one item_no only !

I've create this PHP code to SELECT results only :

$query1 = "SELECT 
                DISTINCT(item_no) AS field, 
                COUNT(item_no) AS fieldCount, 
                COUNT(ref) AS refcount 
            FROM 
                store 
            GROUP BY item_no HAVING fieldCount > 1";

$result1 = mysql_query($query1);

if(mysql_num_rows($result1)>0){
    while ($row1=mysql_fetch_assoc($result1)) {
        echo $row1['field']."<br /><br />";
    }
} else {
    //IGNORE
}

How to tell the query to SELECT Duplicate records properly according to my needs before creating the DELETE query.

Thanks Guys

Possible duplicate of [Delete all Duplicate Rows except for One in MySQL?](http://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql) — Alex Andrei, May 11 '16 at 12:44

Giorgos Betsos · Accepted Answer · 2016-05-11T13:10:40.037

2

You can use the following query to produce the required result set:

SELECT t1.*
FROM store AS t1
JOIN (
   SELECT MIN(id) AS id, ref, item_no
   FROM store
   GROUP BY ref, item_no
) AS t2 ON t1.id > t2.id AND t1.ref = t2.ref AND t1.item_no = t2.item_no

Demo here

To DELETE you can use:

DELETE t1
FROM store AS t1
JOIN (
   SELECT MIN(id) AS id, ref, item_no
   FROM store
   GROUP BY ref, item_no
) AS t2 ON t1.id > t2.id AND t1.ref = t2.ref AND t1.item_no = t2.item_no

edited May 11 '16 at 13:10

answered May 11 '16 at 12:46

Giorgos Betsos

71,379
9
63
98

Thanks for your answer, i'll try the code you have informed me with. I'll reply back shortly – Ali Hamra May 11 '16 at 12:52
I've tried it, but it doesn't select the duplicate. It lists all `item_no` . Also why did you select id ? I want ref and supplier if they are the same also – Ali Hamra May 11 '16 at 12:55
@AliHamra The `SELECT` query selects everything *but* the duplicates. I thought this was your intention. – Giorgos Betsos May 11 '16 at 12:57
Oh my bad, I want the opposite. I want to list the duplicates – Ali Hamra May 11 '16 at 13:00
@AliHamra Please check the edit I made: `t1.id > t2.id` instead of `t1.id = t2.id` – Giorgos Betsos May 11 '16 at 13:01
I've used your updated answer, but now it does not list any `item_no` at all. And there is no mysql query errors too . – Ali Hamra May 11 '16 at 13:08
@AliHamra I've tested the code and it works with your sample data. You can also check the fiddle demo I've included in the answer. – Giorgos Betsos May 11 '16 at 13:11

score 1 · Answer 2 · answered May 11 '16 at 13:30

1

To find only duplicate records you can use

 SELECT * FROM store WHERE id NOT IN 
 (SELECT id FROM store AS outerStore WHERE id = 
 (SELECT MAX(id) FROM store AS innerStore 
 WHERE outerStore.ref = innerStore.ref AND 
 outerStore.supplier = innerStore.supplier AND outerStore.item_no = innerStore.item_no))

Maybe long, but it should work.

answered May 11 '16 at 13:30

Vladislav Latish

307
1
10

It doesn't matter if its long, the query itself is correct but it outputs empty records :(..I don't understand why – Ali Hamra May 11 '16 at 13:35
Ok I guess I found what is the problem..If I remove `outerStore.ref = innerStore.ref AND outerStore.supplier = innerStore.supplier AND` from your code, it works but not what I am expected...it only list duplicate items – Ali Hamra May 11 '16 at 13:39
It works in my local base with your data. `mysql_num_rows($result1)` return 0 ? – Vladislav Latish May 11 '16 at 13:44

ScaisEdge · Answer 3 · 2016-05-11T13:14:46.153

0

If you want the select of the row to delete use

select *  from  store 
where id not in (
    select max(id)  from  store 
    group by  distinct ref, item_no, supplier);

Or you can directly use a command for direct delete using

delete from  store 
where id not in (
    select max(id)  from  store 
    group by  distinct ref, item_no, supplier);

edited May 11 '16 at 13:14

answered May 11 '16 at 12:49

ScaisEdge

131,976
10
91
107

Thanks for your answer, but it shows MySQL error `mysql_num_rows() expects parameter 1 to be resource .....` – Ali Hamra May 11 '16 at 12:58
I have update the answer with the select for getting the row candidate to delete – ScaisEdge May 11 '16 at 13:02
According to your code it seems that it should work, but i'm still getting the same mysql error :( . – Ali Hamra May 11 '16 at 13:05
Could be there is an error in tbale name .. i have update the answer .. with proper table name – ScaisEdge May 11 '16 at 13:16

PHP - Deleting duplicate values with same ref

3 Answers3