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
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,...Prev by Date: ... (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)
