How to delete by combining values with the same department_id?

CMS & WordPress
,
1
nature protection department_Id
Environmental Police 10
Guara Wolf 10
Lear's Macaw 10
Australian Koala 10
animal Ongs 1
illegal loggers 1
animal trafficker 0
illegal loggers 0
illegal loggers 2
animal trafficker 2

  I want to delete all lines of the same department_id that contain illegal nature and animal nature together with traffic protection, if the animal nature contains the protection Ongs have to preserve illegal loggers e protection Ongs with the same department_Id. Remembering that I don't know how to combine the same department_id of the two different protections, I can't put it in the loggers function, how do I do it?

DELETE FROM table WHERE (nature = 'illegal' and nature = 'animal' and protection = 'trafficker') and department_id = department_id

The result must be this:

nature protection department_Id
Environmental Police 10
Guara Wolf 10
Lear's Macaw 10
Australian Koala 10
animal Ongs 1
illegal loggers 1
1 Like
2

To delete all rows with the same department_id that contain both “illegal” nature and “animal” nature with “trafficker” protection, while preserving rows where the “animal” nature has the “Ongs” protection, you can use the following SQL query:

DELETE FROM table
WHERE department_id IN (
    SELECT t1.department_id
    FROM table t1
    JOIN table t2 ON t1.department_id = t2.department_id
    WHERE t1.nature = 'illegal' AND t2.nature = 'animal'
    AND t1.protection = 'Ongs' AND t2.protection = 'trafficker'
)
4

Why is that? Looks fine to me :person_shrugging:

1 Like
5

You are right. I didn’t get the join correct

6

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.