Teradata supports multiple ways to delete data from a table using another table.

Teradata: Delete From Table

There are many scenarios where it is required to delete data either directly from a table or using another table.

Creating Tables: Creating 2 tables with similar structure to understand the logic in details.

Table 1
CREATE MULTISET VOLATILE TABLE deletetable(
   id INTEGER,
   name VARCHAR(100)
)
PRIMARY INDEX(id)
ON COMMIT PRESERVE ROWS;

Table 2
CREATE MULTISET VOLATILE TABLE othertable(
   id INTEGER,
   name VARCHAR(100)
)
PRIMARY INDEX(id)
ON COMMIT PRESERVE ROWS;


Populating Tables: Inserting relevant data in newly created tables for understanding logic.

Table 1:
INSERT INTO deletetable VALUES(1,'Teradata');
INSERT INTO deletetable VALUES(2,'Database');
INSERT INTO deletetable VALUES(3,'Oracle');
INSERT INTO deletetable VALUES(4,'Vertica');

Table 2:
INSERT INTO othertable VALUES(1,'Teradata');
INSERT INTO othertable VALUES(2,'Database');
INSERT INTO othertable VALUES(5,'DB2');
INSERT INTO othertable VALUES(6,'MYSQL');

DELETE FROM deletetable 
WHERE id=4;

Output:
*** Delete completed. One row deleted.

Approach 1 : Delete with the help of other table using direct filter.

DELETE FROM deletetable 
WHERE deletetable.id=othertable.id;

Output:
*** Delete completed. Two row deleted.


Approach 2 : Delete with the help of other table using join.

DELETE deletetable
  FROM othertable  
WHERE deletetable.id = othertable.id;

Output:
*** Delete completed. Two row deleted.


Approach 3 : Delete with the help of other table using traditional WHERE clause join.

DELETE FROM deletetable, othertable  
WHERE deletetable.id = othertable.id;

Output:
*** Delete completed. Two row deleted.


Approach 4 : Delete with the help of other table using EXISTS condition.

DELETE FROM deletetable
WHERE EXISTS (SELECT 1 FROM othertable WHERE deletetable.id = othertable.id);

Output:
*** Delete completed. Two row deleted.


Approach 5 : Delete with the help of other table using IN condition. This approach should be avoided.

DELETE FROM deletetable
WHERE id in (SELECT id FROM othertable);

 Output:
*** Delete completed. Two row deleted.


Approach 6 : Delete with the help of other table using Merge join.

MERGE INTO deletetable tgt
USING othertable src
     ON (src.id = tgt.id)
WHEN MATCHED THEN
DELETE ;

 Output:
*** Merge completed. 2 rows affected.
     No rows inserted, no rows updated, 2 rows deleted.

Visit Teradata Upsert page to learn more about MERGE operation.