sql - bulk delete from a many-to-many relation table based on a WHERE condition that checks ids in two lists

Question

Assume that there is an employee table and a project table. A relationship table emp_proj contains following column: (id, emp_id, proj_id)

I would like to delete rows from emp_proj that contain following pairs of (emp_id, proj_id) : (1,101) , (1,102), (2,202), (3,303)

Note that the table may contain other rows with emp_ids 1, 2 and 3.

How would I use WHERE clause that would compare these pairs ?

I thought of using nesting of ORs but that would complicate the query.

Also I would like to do a bulk delete operation that in one-go, would delete all rows that contain the concerned pairs.

Answer 1

On Postgres, your suggested approach might be the only option:

DELETE
FROM emp_proj
WHERE
    emp_id = 1 AND proj_id = 101 OR
    emp_id = 1 AND proj_id = 102 OR
    emp_id = 2 AND proj_id = 202 OR
    emp_id = 3 AND proj_id = 303;

Note that on certain other databases, such as MySQL, you could have used a tuple syntax which is a bit more terse:

DELETE
FROM emp_proj
WHERE
    (emp_id, proj_id) IN ((1, 101), (1, 102), (2, 202), (3, 303));