I was recently asked by a team member how to go about deleting records from a table based on an arbitrary composite key that was determined by the results of separate query that leveraged one or more tables, and I figured I’d post this solution for everyone.
The query below gives an example of using an arbitrary composite key comprised of two fields; however, the example can easily be leveraged to work for composite keys that consist of less or more fields. It has also been significantly simplified for instructional purposes.
In short, the query will delete from TABLE1 where it successfully joins between TABLE1 and TABLE2. This query DOES NOT require that either field be a PRIMARY or FOREIGN key, just that a successful join can be made between the two tables.
DELETE FROM TABLE1 AS TB1 WHERE EXISTS ( SELECT 1 FROM ( SELECT FIELD1, FIELD2 FROM TABLE2 ) AS TB2 WHERE (TB1.FIELD1 = TB2.FIELD1 AND TB1.FIELD2 = TB2.FIELD2) );
NOTE: This query was designed to work for DB2, and may need modifications to work on other database platforms.