![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 4 of 4 total |
![]() |
Wed, Sep 30 2009 12:02 PM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Wed, Sep 30 2009 12:09 PM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Wed, Sep 30 2009 1:02 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Roy,
<< However, I'm still interested in better solutions >> The best solution is cascading deletes with RI. ![]() -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Oct 1 2009 3:58 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |