I have the following table structure that define the relationship between posts and their categories.
Post_id | Category_id
---------------------
1 | A
1 | B
1 | C
2 | A
2 | B
2 | C
2 | D
So i want to search the post that have two given categories (For example A and B) and then delete the row A. So the result would be:
Post_id | Category_id
---------------------
1 | B
1 | C
2 | B
2 | C
2 | D
How can achieve this?
Try this:
delete t1
from yourtable t1
join (
-- This sub query will retrieve records that have both A and B for each `Post_id`
select Post_id
from yourtable
where Category_id in ('A', 'B')
group by Post_id
having count(distinct Category_id) = 2
) t2
-- Then join this sub query on your table.
on t1.Post_id = t2.Post_id
where t1.Category_id = 'A'
You can find the posts by doing:
select post_id
from post_categories
where category_id in ('A', 'B')
group by post_id
having count(distinct category_id) = 2;
You can then delete them with a join:
delete pc
from post_categories pc join
(select post_id
from post_categories
where category_id in ('A', 'B')
group by post_id
having count(distinct category_id) = 2
) todelete
on pc.post_id = todelete.post_id
where pc.category_id = 'A';
