Delete from Table with Composite Key and joining to another table?

I need to delete from one table with a composite key while joining to another table. Here is an example that doesn't work:

delete from New_ATM_FIELD_DEF where
(TABLE_NAME,FIELD_NAME,INT_VALUE,CHAR_VALUE) in (   select a.TABLE_NAME, a.FIELD_NAME, a.INT_VALUE, a.CHAR_VALUE from New_ATM_FIELD_DEF a, ATM_FIELD_DEF b

    where (a.TABLE_NAME = b.TABLE_NAME

           or (a.TABLE_NAME is null and b.TABLE_NAME is null))
      and (a.FIELD_NAME = b.FIELD_NAME
           or (a.FIELD_NAME is null and b.FIELD_NAME is null))
      and (a.FIELD_TYPE = b.FIELD_TYPE
           or (a.FIELD_TYPE is null and b.FIELD_TYPE is null))
      and (a.CROSS_TABLE_NAME = b.CROSS_TABLE_NAME
           or (a.CROSS_TABLE_NAME is null and b.CROSS_TABLE_NAME is
null))
      and (a.CROSS_FIELD_NAME = b.CROSS_FIELD_NAME
           or (a.CROSS_FIELD_NAME is null and b.CROSS_FIELD_NAME is
null))
      and (a.POPLIST_TYPE = b.POPLIST_TYPE
           or (a.POPLIST_TYPE is null and b.POPLIST_TYPE is null))
      )

I am essentially trying to delete all the records from the new table that also exist in the original table. This is very easy for tables with a single part key.

Any help will be greatly appreciated.

chris hardison

Received on Wed Jan 06 1999 - 17:46:59 CST