homepage Welcome to WebmasterWorld Guest from 54.145.191.14
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

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



 
Msg#: 4276544 posted 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

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4276544 posted 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

);

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved