CODEWITHSUNDEEP
mysqlsql
Aurel Drejta
Aurel Drejta

Reputation: 555

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

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?

Upvotes: 0

Views: 32

Answers (3)

forpas
forpas

Reputation: 164064

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.

Upvotes: 1

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30545

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
)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269483

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;

Upvotes: 0

Related Questions