Delete records from multiple tables

Question

Consider these several tables:

tab1
-------
userid
email
address
environment

tab2

-------
ecode
company
policy

tab3
-------
id
pan no.
dl no.

Here tab1 is the parent table. I want to delete all the userid of tab1 from tab2 and tab3. Either userid will be in tab1 or tab2 or both. These tables are environment specific means environment/tab1 and environment/tab2.

How can this be done with a single query?

Answer 1

I'm not an expert with the AS400, but I don't know of any way to do that in any SQL dialect. You need to start a transaction, perform your separate deletes, then commit the transaction if you want to make sure that it the action is atomic (meaning that the whole thing runs or none of it runs).

The code would probably look something like this:

BEGIN TRANSACTION

DELETE FROM tab2
WHERE userid IN (SELECT userid FROM tab1)

DELETE FROM tab3
WHERE userid IN (SELECT userid FROM tab1)

COMMIT TRANSACTION

You don't specify exactly how those tables are related, so I took a guess. Also, you should probably have some error handling that issues a ROLLBACK if an error is encountered. I don't know the error handling syntax for AS400 (DB2?), so you'll need to look that up.