MySQL remove rows with value that exists in rows with certain value

[binarybulker]

I am trying to remove rows that have the same column value in other rows with a separate column value.

Here is an example.

I want to take the following:

id, cid, value
1 , 1 , john
2 , 1 , adam
3 , 1 , frank
4 , 2 , jim
5 , 2 , frank
6 , 2 , dean
7 , 3 , adam
8 , 3 , john

And remove rows in `cid`=1 where the value is the same as in `cid`=3:

id, cid, value
3 , 1 , frank
4 , 2 , jim
5 , 2 , frank
6 , 2 , dean
7 , 3 , adam
8 , 3 , john

If the database is huge, using PHP to loop through `cid`=3 and delete ones that exist in `cid`=1 will take many hours to execute.

[timster]

Welcome to webmasterworld.

You can find the similar rows inside the same table with a self join. But I don’t think you will be able to delete the rows from the same table in the same query.

The solution is to create a duplicate table to select from. Then select the data from the duplicate table, and then to delete the data in the first table.

Here is example code. Please test it, and when you are sure it does what you want, replace the “select *” with “delete”. Of course, you’ll have to replace the names of the tables as well.

select *
from test
where id in (

select t1.id
from test_copy t1
join 
test_copy t2 on 
t1.value = t2.value
where t1.cid = 1
and t2.cid = 3

);