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

My solution has been to concatinate the key fields like this:

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))
      )

Chris Hardison wrote:
>
> 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
>
> ------------------------------------------------------------------------
>
> Chris Hardison <chris.hardison_at_interpath.net>
> Database Designer
> Interpath Communication, Inc.
>
> Chris Hardison
> Database Designer <chris.hardison_at_interpath.net>
> Interpath Communication, Inc.
> Interpath Communications Work: 919.388.6211
> 1700 Perimeter Park Fax: 919.834.3842
> Research Triangle Park Netscape Conference Address
> NC Netscape Conference DLS Server
> 27709
> US
> Additional Information:
> Last Name Hardison
> First Name Chris
> Version 2.1

Received on Thu Jan 07 1999 - 13:24:54 CST