I am just trying to delete records

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

SQL Server 2000 Forums

SQL Server Development (2000)

I am just trying to delete records

Author

Topic

jesus4u
Posting Yak Master

204 Posts

Posted - 2003-02-06 : 08:10:58

I am just trying to delete records other than the ones with the WHERE condition but it still deletes them all!

Why?


delete from sitesvisited  

WHERE     exists (SELECT     dbo.SitesVisited.*, dbo.Members.TypeOfMembership AS Expr1
FROM         dbo.SitesVisited INNER JOIN
                      dbo.Members ON dbo.SitesVisited.MemberID = dbo.Members.MemberID
WHERE     (NOT (dbo.Members.TypeOfMembership = 'pri')) or (NOT (dbo.Members.TypeOfMembership = 'pre')))

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-02-06 : 08:20:08

You've misconstructed your correlated subquery. You need to reference a row from you outer query within your EXISTS construct...

But my guess is EXISTS is not what you want anyway ...


delete sitesvisited
from sitesvisited s inner join members m on s.memberid = m.memberid
where m.typeofmembership not in ('pri','pre')

Jay White
{0}

jesus4u
Posting Yak Master

204 Posts

Posted - 2003-02-06 : 10:28:42

Actuall i got it like this:


delete SitesVisited  from Members

where SitesVisited.MemberID = Members.MemberID

and not Members.TypeOfMembership = 'pri' and not Members.TypeOfMembership = 'pre'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources