All Forums Database
sdc 13 posts Joined 06/15
12 Oct 2015
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?

tomnolan 594 posts Joined 01/08
12 Oct 2015

Use an IN-predicate that contains a SELECT subquery, like this:
 
delete from ChildTable where ChildKey in (select ParentKey from ParentTable where ... )

sdc 13 posts Joined 06/15
12 Oct 2015

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.

dnoeth 4628 posts Joined 11/04
12 Oct 2015

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.

Dieter

You must sign in to leave a comment.