Where Exists - SQL Server Forums

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]

rgrene
Starting Member

2 Posts

Posted - 2013-10-21 : 10:07:20

thank you khtan for the timely reply!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources