| MySQL remove rows with value that exists in rows with certain value
|
binarybulker
msg:4276546 | 1:26 pm on Mar 4, 2011 (gmt 0) |
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
msg:4277298 | 11:28 pm on Mar 5, 2011 (gmt 0) |
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
);
|
|
|
