Welcome to WebmasterWorld Guest from 54.167.0.111

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

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

     

binarybulker

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

11:28 pm on Mar 5, 2011 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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

);
 

Featured Threads

Hot Threads This Week

Hot Threads This Month