Re: DELETE and Ref. Integr.

Hi Dirk,

I assume that your situation is like this: Table_1 has a foreign key field which matches a linked field in Table_2. But some of the rows of Table_2 have been deleted, and now you want to delete those rows in Table_1 that link to non-existant rows in Table_2.

In that case you can try:
DELETE FROM Table_1
WHERE Table_1.Foreign_Key_Field NOT IN
(SELECT DISTINCT Linked_Field FROM Table_2);

As always, it is a good idea to make a copy of Table_1 before performing a mass delete.

Sincerely, Dale Shultz

"Dirk Hollweg" <dirk.hollweg_at_bbvag.de> wrote in message news:<9tvqoq$b3m$01$1_at_news.t-online.com>...
> How can i DELETE all rows of a table, that don't fit the rules of referential integrity ?
>
> DELETE stops on first row, which has an pendant in an other table, and rolls back all the transctions.
>
> Any hints ?
>
> Thx in Advance
>
> Di'HELPMEPLEASE'rk
Received on Tue Nov 27 2001 - 11:41:21 CST