Fosfor Fosfor - 3 months ago 9
MySQL Question

Delete with multiple where on different rows for same id - MySQL

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?

JPG JPG
Answer

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'

Demo Here

Comments