SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Where Exists
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rgrene
Starting Member

USA
2 Posts

Posted - 10/20/2013 :  13:29:11  Show Profile  Reply with Quote
Example 1 using WHERE EXISTS deletes the entire data base, yet sample 2 select gives me the correct count. Any suggestions?

Example 1 using where exists:
delete from calls
WHERE EXISTS
(select calls.debtor , calls.client , debtor.closeddate, debtor.debtorid, debtor.clientid from calls, debtor
where calls.Client=debtor.ClientId and calls.debtor=debtor.debtorid
and (debtor.closeddate < '01/01/08' and debtor.ClosedDate is not null))

Example 2 just with select part:
(select calls.debtor , calls.client , debtor.closeddate, debtor.debtorid, debtor.clientid from calls, debtor
where calls.Client=debtor.ClientId and calls.debtor=debtor.debtorid
and (debtor.closeddate < '01/01/08' and debtor.ClosedDate is not null))

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 10/21/2013 :  03:29:35  Show Profile  Reply with Quote
The DELETE deletes all rows becuase
1. the query in the EXISTS returns rows,
2. the exists query does not depends on the calls table in DELETE clause

you could write it like

DELETE C
FROM   calls C
WHERE  EXISTS
(
    select *
    from   debtor D
    where  C.Client = D.ClientId 
    and    C.debtor = D.debtorid
    and    D.closeddate < '01/01/08' 
    and    D.ClosedDate is not null
)


or simply

DELETE C
from   calls C
       INNER JOIN debtor D ON  C.Client = D.ClientId 
                           AND C.debtor = D.debtorid
where  D.closeddate < '01/01/08' 
and    D.ClosedDate is not null



KH
Time is always against us

Go to Top of Page

rgrene
Starting Member

USA
2 Posts

Posted - 10/21/2013 :  10:07:20  Show Profile  Reply with Quote
thank you khtan for the timely reply!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000