Delete records with other roles

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 records with other roles

Author

Topic

getur.srikanth@gmail.com
Yak Posting Veteran

77 Posts

Posted - 2009-09-23 : 10:46:06

I have couple records like this. How can delete only RoldID='1E36A840-2EBB-44EC-8861-0E3D262AC676' records ,If they already have RoldID='0B54F223-E0D4-4CFC-84C3-7C98C1BFC6DA' in that table?

rocknpop
Posting Yak Master

201 Posts

Posted - 2009-09-23 : 11:20:35

IF EXISTS(SELECT 1 FROM YOURTABLE WHERE RoldID='0B54F223-E0D4-4CFC-84C3-7C98C1BFC6DA')
DELETE FROM YOURTABLE WHERE RoldID='1E36A840-2EBB-44EC-8861-0E3D262AC676'

--------------------
Rock n Roll with SQL

getur.srikanth@gmail.com
Yak Posting Veteran

77 Posts

Posted - 2009-09-23 : 11:47:39

How can I get number of user count , Who have both roles.?

rocknpop
Posting Yak Master

201 Posts

Posted - 2009-09-23 : 12:15:37

SELECT TABLE.UserId,COUNT(*)OVER()AS TOTALCOUNT FROM TABLE INNER JOIN
(SELECT NAME FROM TABLE WHERE ROLE=2) T1 ON
TABLE.NAME=T1.NAME WHERE ROLE=1

--------------------
Rock n Roll with SQL

getur.srikanth@gmail.com
Yak Posting Veteran

77 Posts

Posted - 2009-09-24 : 15:28:39

DECLARE @UID uniqueidentifier

SELECT TOP 1 @UID=dbo.aspnet_UsersInRoles.UserId FROM dbo.aspnet_UsersInRoles INNER JOIN
(SELECT UserId FROM dbo.aspnet_UsersInRoles WHERE ROLEID='0b54f223-e0d4-4cfc-84c3-7c98c1bfc6da') T1 ON
dbo.aspnet_UsersInRoles.UserId=T1.UserId WHERE ROLEID='1e36a840-2ebb-44ec-8861-0e3d262ac676'

delete from dbo.aspnet_UsersInRoles where userid=@UID and RoleID='1e36a840-2ebb-44ec-8861-0e3d262ac676'

I am able to delete only one record. How can i delete all records? Whats wrong in query?

rocknpop
Posting Yak Master

201 Posts

Posted - 2009-09-25 : 09:30:29

Do you have an identity column in your table. If yes then you can use this, here Id is the identity:

Delete From dbo.aspnet_UsersInRoles Where Id In (
SELECT dbo.aspnet_UsersInRoles.Id
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'
)

--------------------
Rock n Roll with SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources