Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 Article Discussion
 Where Exists

Author  Topic 

rgrene
Starting Member

2 Posts

Posted - 2013-10-20 : 13:29:11
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)

17689 Posts

Posted - 2013-10-21 : 03:29:35
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
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

rgrene
Starting Member

2 Posts

Posted - 2013-10-21 : 10:07:20
thank you khtan for the timely reply!
Go to Top of Page
   

- Advertisement -