5

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."

Paul Hooper
  • 839
  • 2
  • 7
  • 15

2 Answers2

11

Found this is done by:

DELETE FROM
    table1
  WHERE
    (key1, key2) NOT IN (
      SELECT UNIQUE key1, key2 FROM table2
    );
Paul Hooper
  • 839
  • 2
  • 7
  • 15
3

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
                )
Thomas
  • 63,911
  • 12
  • 95
  • 141
  • Would this offer any performance advantage over the answer I provided? – Paul Hooper Dec 15 '10 at 16:43
  • @Paul Hooper - The only way to know for sure is to analyze the execution plan and do some testing. If Table2 is significantly larger and has many rows for each matching row in Table1, it may perform better because it can stop process on finding the first item. – Thomas Dec 15 '10 at 20:27