Deleting duplicate records from different tables

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)

Deleting duplicate records from different tables

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-07-04 : 08:51:03

Alan writes "I need to delete duplicate records from my SQL 7.0 database SP2. OS: Windows 2000 Server.

I have been using some of the solutions provided by graz on 3/26/01.

Here is what I have done:
1. First I want to create a temporary table with a list of memberid's I want to keep. note: memberid is a primary key in table member

I used:
select computerno, RecCount=count(*), MembertoKeep = min(memberid)
into resignedtemp
from resignedmembers
group by computerno
having count(*) > 1
order by count(*) desc, computerno

result in table resignedtemp


computerno    RecCount    MembertoKeep 
--------------------------------------
2390025         13          206216
2390165         13          212573

2. Then I want to delete all records having duplicate computernos in "resignedmembers", except those with memberid's in table "resignedtemp", from table "member"

delete from member
where memberid in 
		(select memberid from resignedmembers
		where computerno 
			in (
			select computerno from resignedmembers
			group by computerno having count(*) > 1))


and where memberid not in (
		select memberid from resignedtemp)

I get this message:
Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'where'.

Can you please help me here.

Thanks
Alan"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources