remove duplicate records? - 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

General SQL Server Forums

New to SQL Server Programming

remove duplicate records?

Author

Topic

getur.srikanth@gmail.com
Yak Posting Veteran

77 Posts

Posted - 2011-03-15 : 15:48:25

I have userid and role id fields in the table. In that table couple userid's has two roles. But it shouldn't.

example:


Userid(FK)    RoleID(FK)
1             1E36A840-2EBB-44EC-8861-0E3D262AC676
1             0B54F223-E0D4-4CFC-84C3-7C98C1BFC6DA

I want to remove '1E36A840-2EBB-44EC-8861-0E3D262AC676' when user has both roles.

How to do that?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-15 : 15:53:32

Search the forums and articles here for "deleting duplicates". This topic is covered at length already.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2011-03-15 : 15:54:47

How do you determine which role is the preferred one?

Very simply you could do a
SELECT UserID, MAX(RoleID) AS RoleID INTO #Distinct
FROM yourTable
GROUP BY UserID

DELETE FROM yourTable

INSERT INTO yourTable
SELECT * FROM #Distinct

But this won't discriminate - it will keep the role ID with the highest value and lose the rest.

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-15 : 16:03:20

How do you know which "Role" to remove????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

getur.srikanth@gmail.com
Yak Posting Veteran

77 Posts

Posted - 2011-03-15 : 16:09:48

That is true. But In my case. That article will help to me.


Userid(FK)    RoleID(FK)
1             1E36A840-2EBB-44EC-8861-0E3D262AC676
1             0B54F223-E0D4-4CFC-84C3-7C98C1BFC6DA
2             1E36A840-2EBB-44EC-8861-0E3D262AC676
3             0B54F223-E0D4-4CFC-84C3-7C98C1BFC6DA
4             1E36A840-2EBB-44EC-8861-0E3D262AC676
4             0B54F223-E0D4-4CFC-84C3-7C98C1BFC6DA

In this case I have to delete 1 and 4 where '1E36A840-2EBB-44EC-8861-0E3D262AC676' which has '0B54F223-E0D4-4CFC-84C3-7C98C1BFC6DA'

quote:
Originally posted by tkizer

Search the forums and articles here for "deleting duplicates". This topic is covered at length already.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-15 : 16:19:51

ok...so how did you come to that decision, i.e., is there some "logic" you can apply to all the rows?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources