CODEWITHSUNDEEP
mysqlsqlsql-delete
user2064467
user2064467

Reputation: 375

Please help me out with the sql query which I'm trying to write

I want to delete IITESE from all the compartmentID whose parent is CTSC. but i got this error saying

Msg 512, Level 16, State 1, Line 3 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

My query is 

delete  from [KRISADMIN].[CompartmentACL] where CompartmentID = (select CompartmentID from [KRISADMIN].[Compartment] where Name = 'CTSC') and 
TrusteeID = (select RoleID from [KRISADMIN].[Role] where Name = 'IITSE') and TrusteeType = 2

Please excuse my query if it is not good.

Upvotes: 0

Views: 42

Answers (1)

Raging Bull
Raging Bull

Reputation: 18737

You can use = when the subquery returns only 1 value.

When subquery returns more than 1 value, you will have to use IN or EXISTS:

Using IN:

delete  from [KRISADMIN].[CompartmentACL] 
where CompartmentID IN (select CompartmentID 
                        from [KRISADMIN].[Compartment] 
                        where Name = 'CTSC') 
and TrusteeID IN (select RoleID 
                  from [KRISADMIN].[Role] 
                  where Name = 'IITSE') 
and TrusteeType = 2

Read more here.

EDIT:

You need to replace = with IN:

delete from [KRISADMIN].[CompartmentACL] 
where CompartmentID IN (select CompartmentID 
                        from [KRISADMIN].[Compartment] 
                        where Name in (Select Name 
                                       from [KRISADMIN].[Compartment] 
                                       where ParentID IN (Select ParentID 
                                                         from [KRISADMIN].[Compartment] 
                                                         where Name = 'CTSC') )) 
and TrusteeID IN (select RoleID 
                  from [KRISADMIN].[Role] 
                  where Name = 'IITSE') 
and TrusteeType = 2

Upvotes: 2

Related Questions