Consider following, simple, relational database model:
We have a list with documents and a table linking these document to something unimportant. The relation between DOCUMENT and LINK is one-to-one or one-to-none.
Now imaging that we’d have to delete all documents where the type column has the value ‘TEMPORARY’.
DELETE FROM LINK WHERE TYPE = 'TEMPORARY' @
If we’d execute this query, we will not be able to delete the correct elements from the DOCUMENT table anymore.
Starting the other way around:
DELETE FROM DOCUMENT WHERE ID IN (SELECT ID FROM LINK WHERE TYPE = 'TEMPORARY') @
won’t work either because the foreign key from LINK to DOCUMENT is restricting the deletion.
Fortunately, DB2 allows us to do multiple deletes in a single query:
WITH -- DELETE THE LINKS FIRST AND LET DB2 STORE THE IDs IN AN 'OLD TABLE' DELETED_LINKS(ID) AS (SELECT ID FROM OLD TABLE ( DELETE FROM LINK WHERE type = 'TEMPORARY' ) ), -- DELETE THE DOCUMENTS USING THE DELETED_LINK IDs DELETED_DOCUMENTS(ID) AS (SELECT ID FROM OLD TABLE ( DELETE FROM DOCUMENT WHERE document IN (SELECT DISTINCT ID FROM DELETED_LINKS) ) ) ( -- SELECT IN WITH STATEMENT IS MANDATORY -> USELESS STATEMENT SELECT * FROM SYSIBM.SYSDUMMY1 ) @
What this query will do is delete all LINK elements where TYPE is ‘TEMPORARY’ and store the IDs from the delete elements in a temporary table called DELETED_LINKS which has a single column named ID.
The next query will use the values from this temporary table to delete the correct rows from the DOCUMENT table.
Last but not least, a completely useless SELECT statement because otherwise it won’t work 😉
Ps. I used this online schema designer.