Deleting duplicates with a where clause

Question

Hey so I have one set of data with the structure:

id     product_number      product_type
1      1001                car
2      1002                house

But the data has some duplicates where:

id     product_number      product_type
1      1001                car
2      1001                house

I need to delete the duplicates but only the value which is = house.

In my mind the query should be like:

DELETE *
FROM table
WHERE product_number is duplicate AND product_type = house

Thanks

Duplicated product_number and/or id? – jarlh Dec 03 '15 at 15:52 — jarlh, Dec 03 '15 at 15:52
Hi yes updated question, duplicates of product_number – Stefkay Dec 03 '15 at 15:53 — Stefkay, Dec 03 '15 at 15:53

score 0 · Accepted Answer · answered Dec 03 '15 at 15:52

In MySQL, you can do what you want with a join:

delete t
    from table t join
         (select product, count(*) as cnt
          from table
          group by product
         ) tt
         on tt.product = t.product
    where tt.cnt > 1 and t.product_type = 'house';

Akshey Bhat · Answer 2 · 2015-12-03T16:07:34.157

0

DELETE *
FROM table
WHERE id not in 
 (select max(id) from table  
       group by product_number)

AND product_type = house

edited Dec 03 '15 at 16:07

answered Dec 03 '15 at 15:56

Akshey Bhat

8,227
1
20
20

fthiella · Answer 3 · 2015-12-03T16:07:12.643

0

You can use an UPDATE query with a join like this:

delete t1.*
from
  t t1 INNER JOIN t t2
  ON t1.product_number = t2.product_number
     AND t1.product_type='house' AND (
       t2.product_type<>'house'
       OR t1.id>t2.id
     )

edited Dec 03 '15 at 16:07

answered Dec 03 '15 at 15:57

fthiella

48,073
15
90
106

score 0 · Answer 4 · edited May 23 '17 at 10:27

You have multiple methods:

1. You can use this query:

INSERT INTO new_table (SELECT DISTINCT * FROM old_table WHERE product_type = house)

2. You can alter your table and change your product number column to add an index to it

ALTER IGNORE TABLE jobs
ADD UNIQUE INDEX idx_name (product_number);

So the duplicated row with the same product number will be dropped automatically, and for more see this link here

3. You can try this query to:

DELETE n1 FROM table_product p1, table_product p2 WHERE p1.product_number = p2.product_number AND p1.product_type= house

Deleting duplicates with a where clause

4 Answers4