Within transaction, how to find keys from one table and use those to delete from other tables in a single request?

Database
sdc
Enthusiast

Within transaction, how to find keys from one table and use those to delete from other tables in a single request?

Hello,

As the subject states, I am trying to find keys in one table and then use those keys to delete from multiple other tables.  I need to do this within a transaction where other things are happening before and after this action.  I am not happy with the only way that I know of to do this:

BEGIN TRANSACTION
<unrelated stuff happens>
select key from table_with_keys where ...
<store resulting keys to variable in application>
<use stored keys to delete from other tables with a DELETE FROM ... WHERE key IN ...>
<unrelated stuff happens>
END TRANSACTION

I don't like this pattern because I am asking Teradata for some information and then turning around and sending it back to Teradata to do something else.  That costs time that I don't have.  To avoid this, I believe that there must be some way to do this in one request to the database.

Things I've tried:

  1. WITH clause, a.k.a. common table expressions (problem: only supports SELECT statement, not DELETE)
  2. CREATE VOLATILE TABLE (problem: can't figure out how to do this multiple times within a transaction since creating table requires ET immediately after)

With either these or other solutions, how can I select keys from the one table and use them to delete from other tables in one request and within a larger transaction?

3 REPLIES
Teradata Employee

Re: Within transaction, how to find keys from one table and use those to delete from other tables in a single request?

Use an IN-predicate that contains a SELECT subquery, like this:

delete from ChildTable where ChildKey in (select ParentKey from ParentTable where ... )

sdc
Enthusiast

Re: Within transaction, how to find keys from one table and use those to delete from other tables in a single request?

Hi tomnolan,

Thanks for your suggestion.  I think that if I do that, Teradata will do the subquery once for every table even though the result will always be the same.  I am trying to avoid that since I expect the subquery to take a significant amount of time.

I really think I need some way of caching the results of the subquery to reuse multiple times when deleting from the other tables.

Regardless, thanks for your help.

Senior Apprentice

Re: Within transaction, how to find keys from one table and use those to delete from other tables in a single request?

Either repeat the subquery multiple times and run all the DELETEs as a Multistatement Request using BEGIN/END REQUEST (the optimizer should create the result only once) or use a Global Temporary Table, then it's a simple INSERT/SELECT within the transaction.