how would you write this sql to delete if either of two fields are in a set of values

Question

Background:

---

i have 2 tables:

Components: (id, name)

Dependencies: (id, hasaComponentId, isaComponentId)

in this case the hasaComponentId and isaComponentId are both foreign keys into the components table joined by components.id

Question

---

i have a set of Ids that the user selects. I want a sql query that will delete records from the dependencies tables where any of the ids in my list of ids is either in the hasaComponentId field or the isaComponentId field.

what is the best sql for this action?

Answer 1

DELETE FROM Dependencies WHERE hasaComponentId In (YOUR OTHER QUERY);
DELETE FROM Dependencies WHERE isaComponentId  In (YOUR OTHER QUERY);

OR

DELETE FROM Dependencies WHERE hasaComponentId In (YOUR OTHER QUERY) OR 
                               isaComponentId  In (YOUR OTHER QUERY);

Answer 2

DELETE Dependencies
FROM Dependencies d, Components c
WHERE
 (  
  (c.id=d.hasaComponentID and d.isaComponentID is null)
  OR
  (c.id=d.isaComponentID and d.hasaComponentID is null)
 )
     AND c.id IN (1,2,3,5)

Not tested, but looks right. Make sure you perform a SELECT first!