Delete from multiple tables in DB2 in a single query

Consider following, simple, relational database model:

Document DB model
Document DB 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.

Advertisements

One thought on “Delete from multiple tables in DB2 in a single query

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s