duplicate horrors - 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

SQL Server 2005 Forums

Transact-SQL (2005)

duplicate horrors

Author

Topic

Harry C
Posting Yak Master

148 Posts

Posted - 2010-01-06 : 15:34:20

I am upgrading a system for a new client. The old Database, which is in SQL 2005 has a table called Users

SELECT Email, Count(Email)
FROM dbo.Users
GROUP BY Email
HAVING Count(Email) > 1

Running this query gave me nightmares. I ended up with 1720 rows and it continues to grow each day. Something the old system is doing is creating duplicate accounts in certain situations.

So, my question is this. From that Users Table, how can I see which UserIDs are actually keyed to other tables and which ones are stand alone. Any that are stand alone, I would like to delete from the DB and then put a unique constraint on the table. Here is the table structure of Users and an example of another table that it is tied to. Basically I want to see what UserIDs do not exist in the UserRace table.

This tells me that

SELECT * FROM Users WHERE UserID NOT IN(SELECT UserID FROM UserRace) AND (RaceADMIN = 0)

But, what I really want is more complex. I really only want a list of DUPLICATES in the Users table that are not in the UserRace table.

So, if a user has two accounts, I want to delete the one with the greater UserID. Make any sense? Thanks alot!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-06 : 16:00:11

What you need is something like this

SELECT TOP(0)	*,
		CAST(NULL AS INT) AS recID
INTO		dbo.UsersDuplicates
FROM		dbo.Users


DELETE	f
OUTPUT	deleted.*
INTO	dbo.UsersDuplicates
FROM	(
		SELECT	*,
			ROW_NUMBER() OVER (PARTITION BY Email ORDER BY UserID) AS recID
		FROM	dbo.Users
	) AS f
WHERE	recID >  1

CREATE UNIQUE NONCLUSTERED INDEX IX_Email ON dbo.Users (Email) INCLUDE (UserID)

N 56°04'39.26"
E 12°55'05.63"

Harry C
Posting Yak Master

148 Posts

Posted - 2010-01-06 : 17:01:01

Peso, thank you so much. I have to test that our further, but that looks like it will work at first glance. I wish I could actually tell what was going on!! But, I will try to dig through that and learn. I appreciate it. Thanks

HC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 00:51:37

Also, If you want to identify reffered tables which uses values from yourtable you can make use of system view sys.foreign_keys provided you've set foreign key relation

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources