CODEWITHSUNDEEP
sqlteradata
Paul Hooper
Paul Hooper

Reputation: 849

How to delete rows in a Teradata table that are not in another table?

What makes my situation tricky is that I don't have a single column key, with a simple list of primary keys to delete (for instance, "delete from table where key in ([list])"). I have multiple columns together as the primary key, and would need to join on all of them.

Using what I know of other databases, I thought this might be done as:

DELETE FROM
    table1 t1
  LEFT OUTER JOIN
      table2 t2
    ON
      t2.key1 = t1.key1 AND
      t2.key2 = t1.key2
  WHERE
    t2.key1 IS NULL;

But Teradata (v12) responds with error number 3706, saying "Syntax error: Joined Tables are not allowed in FROM clause."

Upvotes: 5

Views: 39583

Answers (2)

Thomas
Thomas

Reputation: 64654

Another way is to use a correlated subquery:

Delete From Table1
Where Not Exists(
                Select 1 
                From Table2 
                Where Table2.key1 = Table1.key1
                    And Table2.key2 = Table1.key2
                )

Upvotes: 3

Paul Hooper
Paul Hooper

Reputation: 849

Found this is done by:

DELETE FROM
    table1
  WHERE
    (key1, key2) NOT IN (
      SELECT UNIQUE key1, key2 FROM table2
    );

Upvotes: 12

Related Questions