Delete Records Using an Arbitrary Composite Key Determined by Subquery

Delete Records Using an Arbitrary Composite Key Determined by Subquery

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.

Author: daharveyjr

I’m a solution architect responsible for the design, development, implementation, testing, and maintenance of e-commerce operations and applications using the Hybris and WebSphere Commerce product suites and other web technologies such as Java, J2EE/JEE, Spring, PHP, WordPress and more. Twitter | Facebook | LinkedIn

Leave a Reply

Your email address will not be published.