I'm trying to build a deletion script. Most of it is pretty simple but one bit is doing my head in.

Three tables

Companies
Contacts
Career

Contacts can have many career entries

So to give a simple example

Companies c1, c2 and c3

Contacts p1, p2, p3

Career

1, c1, p1
2, c1, p2
3, c2, p2
4, c2, p4
5, p3, c3


If I want to delete c1 I also want to delete  p1 & p2 and 1, 2 & 3 from the career table.

I came up with

DELETE FROM Career WHERE _fkContacts IN (SELECT _ID from Contacts WHERE _ID IN (SELECT _fkcontacts FROM Career WHERE _fkCompanies = :CompanyID))

which works (bit slow though) but then realised I wouldn't be able to delete from the contacts table since I'd loose the link between contacts and companies. If I delete from the contacts table first I loose the link the other way.

Suggestions please

Roy Lambert

As is often the case posting stimulated my brain

I delete from contacts first then

delete from career where _fkcontacts not in (select _id from contacts)


However, I'm still interested in better solutions


Roy Lambert

Roy,

<< However, I'm still interested in better solutions >>

The best solution is cascading deletes with RI.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>The best solution is cascading deletes with RI.

Wash your mouth out! I will move to database enforced RI only when the scars fade or I have a switch to turn it off if necessary.

Roy Lambert