There are many scenarios where it is required to delete data either directly from a table or using another table.
➠ Sample Table and Data
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;
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.
DELETE FROM deletetable
WHERE deletetable.id=othertable.id;
Output:
*** Delete completed. Two row deleted.
DELETE deletetable
FROM othertable
WHERE deletetable.id = othertable.id;
Output:
*** Delete completed. Two row deleted.
DELETE FROM deletetable, othertable
WHERE deletetable.id = othertable.id;
Output:
*** Delete completed. Two row deleted.
DELETE FROM deletetable
WHERE EXISTS (SELECT 1 FROM othertable WHERE deletetable.id = othertable.id);
Output:
*** Delete completed. Two row deleted.
DELETE FROM deletetable
WHERE id in (SELECT id FROM othertable);
Output:
*** Delete completed. Two row deleted.
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.