delete duplate 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

SQL Server 2005 Forums

Transact-SQL (2005)

delete duplate records

Author

Topic

getur.srikanth@gmail.com
Yak Posting Veteran

77 Posts

Posted - 2009-09-29 : 15:55:16

I have couple people have two roles. for example role1 and role2. I want delete all role1 users those who already have role2.

role1 = '1e36a840-2ebb-44ec-8861-0e3d262ac676'
role2 = '0b54f223-e0d4-4cfc-84c3-7c98c1bfc6da'

I got below query to work but I am getting error

Delete From dbo.aspnet_UsersInRoles Where UserId In (
SELECT u1.userid, u1.roleid
FROM
dbo.aspnet_UsersInRoles AS U1 INNER JOIN
dbo.aspnet_UsersInRoles AS U2 ON U1.UserId=U2.UserId
WHERE U1.ROLEID='1e36a840-2ebb-44ec-8861-0e3d262ac676'
AND U2.ROLEID='0b54f223-e0d4-4cfc-84c3-7c98c1bfc6da'
)
error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-29 : 16:07:21

Here's one way to do it. Do the select first and if you like the results then switch it to the DELETE.


select ru.* ur.*
--delete ur
from   (
       select userid
       from   dbo.aspnet_UsersInRoles
       where  roleid = '0b54f223-e0d4-4cfc-84c3-7c98c1bfc6da'
       group by userid
       ) d
inner join dbo.aspnet_UsersInRoles ur
       on ru.userid = d.userid
where  ur.roleid = '1e36a840-2ebb-44ec-8861-0e3d262ac676'

EDIT:
dislexia check

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources