CODEWITHSUNDEEP
mysqlsql
Talib Daryabi
Talib Daryabi

Reputation: 783

how to remove duplicate entries from many to many relation using sql query

My tables look like this. my op and country is having many to many relationships with each other.

OP
id, name,.....

op_country
id, op_id, country_id

country
id, name, ...

my op_country filled like below

id  op_id   country_id
1     1        1
2     1        2
3     2        2
4     2        3
5     3        3
6     3        3
7     1        1

I want to remove my duplicate entries from op_country. Here I want to remove rows 6 and 7 since we already have rows with such values.

How can I do that.

Upvotes: 1

Views: 908

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

You want to delete entries for which exists a sibling with a lower ID:

delete from op_country
where exists
(
  select null
  from (select * from op_country) op2
  where op2.op_id = op_country.op_id
  and op2.country_id = op_country.country_id
  and op2.id < op_country.id
);

The from (select * from op_country) is necessary instead of a mere from op_country due to some weird restriction in MySQL updates.

Upvotes: 1

Akina
Akina

Reputation: 42854

DELETE t1
FROM op_country t1
JOIN op_country t2 USING (op_id, country_id)
WHERE t1.id > t2.id

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=247ebc5870a6ab10b64076ffb375797f

Upvotes: 3

Related Questions