CODEWITHSUNDEEP
sqlteradata
Chris
Chris

Reputation: 1697

delete rows in parent-child tables found in another parent-child tables

I am loading data into a parent-child pair of tables in a "staging" database schema. If there are duplicate records that were previously loaded into a parent-child pair of tables in a "master" database schema, I want to delete them from the "staging" database tables.

This query

SELECT  A.*,B.* 
FROM  STG.AUTO_REPR_PAR_STG A 
 JOIN STG.AUTO_REPR_CHLD_STG B 
 ON A.TEST_SEQ_NUM=B.TEST_SEQ_NUM 
WHERE EXISTS (  SELECT A.*, B.* 
                FROM  MST.AUTO_REPR_PAR A 
                 JOIN MST.AUTO_REPR_CHLD B 
                 ON A.TEST_SEQ_NUM=B.TEST_SEQ_NUM 
             )

will show what's in staging that was previously loaded in master. But how do I delete from the parent-child pair of tables in staging database? I am drawing a "blank"....I tried this but it bombs ("Tables not allowed in FROM clause"):

   DELETE FROM STG.AUTO_REPR_PAR_STG A 
           JOIN STG.AUTO_REPR_CHLD_STG B
           ON A.TEST_SEQ_NUM=B.TEST_SEQ_NUM 
          WHERE EXISTS (SELECT A.*, B.* 
                        FROM  MST.AUTO_REPR_PAR A 
                        JOIN MST.AUTO_REPR_CHLD B
                        ON A.TEST_SEQ_NUM=B.TEST_SEQ_NUM 
                       )

Back-end is Teradata v13. I am currently researching the CASCADE DELETE option but I am not even sure it is supported....Any idea?

Upvotes: 2

Views: 525

Answers (2)

fbglv
fbglv

Reputation: 81

You may try something like this:

  • Instead of a subquery with the EXIST clause, you can use an OUTER JOIN - you select all rows with NULL columns in the target outer table, i.e. the not-matching rows;
  • You save the result of the the previous query into a temporary table, and you run 2 DELETE statements.

An OUTER JOIN is much more efficient compared to a subquery with EXISTS, especially with large data sets.

Upvotes: 0

dnoeth
dnoeth

Reputation: 60482

There's no way to delete from multiple tables in a single DELETE statement, you need one for each table:

DELETE FROM STG.AUTO_REPR_PAR_STG A
WHERE TEST_SEQ_NUM IN (
SELECT A.TEST_SEQ_NUM FROM  MST.AUTO_REPR_PAR A JOIN MST.AUTO_REPR_CHLD B
ON A.TEST_SEQ_NUM=B.TEST_SEQ_NUM )
;DELETE FROM STG.AUTO_REPR_CHLD_STG B
WHERE TEST_SEQ_NUM IN (
SELECT A.TEST_SEQ_NUM FROM  MST.AUTO_REPR_PAR A JOIN MST.AUTO_REPR_CHLD B
ON A.TEST_SEQ_NUM=B.TEST_SEQ_NUM )

If you run this as a Multi Statement Request the join will be done only once.

Upvotes: 3

Related Questions