How to delete one of the duplicates based on another column?

Question

This is a follow-up of the question asked here

Let's say that i have another column called "primary" in the schema presented in the question above so now it would look something like this:

Stones Table:

stone_id = 412 upcharge_title = "sapphire" primary = 1
stone_id = 412 upcharge_title = "sapphire" primary = 0

I want to delete the rows that are duplicates in the "stone_id" and "upcharge_title" fields and have the value on the "primary" field equal to 0 but leave the other row that has the value of primary equal to 1?

How can i do that with MySQL?

I apologize for not following those guidelines. Next time I will try my best to be a bit more descriptive and provide better mock-ups to help others answer my questions when asking questions related to SQL queries. — Aurel Drejta, Apr 23 '20 at 23:11

forpas · Accepted Answer · 2020-04-23T19:54:02.607

1

You can do it with a self join:

delete t1
from tablename t1 inner join tablename t2
on t1.stone_id = t2.stone_id and t1.upcharge_title = t2.upcharge_title 
and t1.primary = 0 and t2.primary = 1

See the demo.

edited Apr 23 '20 at 19:54

answered Apr 23 '20 at 19:46

forpas

160,666
10
38
76

How is this actually working? I can't seem to wrap my head around it – Aurel Drejta Apr 23 '20 at 23:08
1

Every row of the table with primary = 0 is joined to another row with primary = 1 and the same stone_id and upcharge_title (if it exists). So for every such match the row with primary = 0 is deleted. – forpas Apr 24 '20 at 05:43

score 0 · Answer 2 · answered Apr 23 '20 at 19:47

One method is:

delete t
    from t join
         (select stone_id, upcharge_title, max(primary) as max_primary
          from t
          group by stone_id, upcharge_title
         ) tt
         using (stone_id, upcharge_title)
    where t.primary = 0 and tt.max_primary = 1;

score 0 · Answer 3 · answered Apr 23 '20 at 19:49

0

you can use subquery approach

delete from tbl
where primary != 0 and (stone_id, upcharge_title) = (
  select stone_id, upcharge_title
  from tbl
  group by stone_id, upcharge_title
  having count(*) > 1
)

answered Apr 23 '20 at 19:49

Derviş Kayımbaşıoğlu

28,492
4
50
72

How to delete one of the duplicates based on another column?

3 Answers3