Re: Discussing 3 different strategies for deleting from multiple tables

> Method 1 -
>
> DELETE FROM ACCOUNT WHERE ACCOUNTID = ?
> DELETE FROM CONTACT WHERE ACCOUNTID NOT IN (SELECT ACCOUNTID FROM ACCOUNT)
> DELETE FROM OPPORTUNITY WHERE CONTACTID NOT IN (SELECT CONTACTID FROM
CONTACT)

Seems expensive. If you know the AccountID in the 1st SQL statement, why not
use it the the 2nd, 3rd SQL statements rather than getting the SQL engine to
build an entire list of Account ID's and deleting those not in it? A lot of
work and _NOT_ SQL efficient.

Method 4
-------------
(done in the order not to violate FK constraints - but I read what you said)
DELETE FROM OPPORTUNITY WHERE CONTACTID IN (SELECT CONTACTID FROM CONTACT
WHERE ACCOUNTID = ?)
DELETE FROM CONTACT WHERE ACCOUNTID = ?
DELETE FROM ACCOUNT WHERE ACCOUNTID = ?

Stephen Howe


.

Relevant Pages

  • Re: Discussing 3 different strategies for deleting from multiple tables
    ... DELETE FROM CONTACT WHERE ACCOUNTID NOT IN ... DELETE FROM OPPORTUNITY WHERE CONTACTID NOT IN (SELECT CONTACTID FROM ... If you know the AccountID in the 1st SQL statement, ... (done in the order not to violate FK constraints - but I read what you ...
    (microsoft.public.data.ado)
  • complex insert command
    ... I have a list of ID's from a table i am selecting with this sql statement ... select accountid from accounts where accountid not in (select accountid from ... ConfidentialityAgreements) ... already in our confidentiality agreement table... ...
    (microsoft.public.sqlserver.programming)
  • Re: complex insert command
    ... >I am hoping for an easy solution to this so I don't have to do this update ... >I have a list of ID's from a table i am selecting with this sql statement ... >select accountid from accounts where accountid not in (select accountid from ... >already in our confidentiality agreement table... ...
    (microsoft.public.sqlserver.programming)
  • Re: complex insert command
    ... >>I am hoping for an easy solution to this so I don't have to do this update ... >>I have a list of ID's from a table i am selecting with this sql statement ... >>select accountid from accounts where accountid not in (select accountid ... >>already in our confidentiality agreement table... ...
    (microsoft.public.sqlserver.programming)
  • RE: complex insert command
    ... insert into ConfidentialityAgreements (accountID) ... where accountid not in (select accountid from ConfidentialityAgreements) ... "Brian Henry" wrote: ... > I have a list of ID's from a table i am selecting with this sql statement ...
    (microsoft.public.sqlserver.programming)