Solved

Error Msg on Delete query: "Subquery returned more than 1 value."

Posted on 2004-08-14
15
568 Views
Last Modified: 2008-01-09
I want to delete records in Table A based on whether there are records in Table B.

My database tracks Families (Families table). A family can have one or more family members (People table) in it. Groups of people do things together (Groups table). Each person in a family can participate in one or more groups (GroupsPeople table). A family, people and group belong to an entity (Entities table). Primary and foreign keys are like this:

TABLE               PRIMARY AND FOREIGN KEYS
Families            FamilyID (PK)
People              PeopleID (PK), FamilyID (FK)
Groups             GroupID (PK)
GroupsPeople    GroupID (FK), PeopleID (FK), EID (FK)  -- All three together are a composite PK
Entities             EID (PK)

I want to delete from the GroupsPeople table all records for people in a family. Here is the SQL:

DECLARE @EID int
DECLARE @FamilyID int
DECLARE @GroupID int

SET @EID = 1
SET @FamilyID = 8
SET @GroupID = 5

            DELETE FROM GroupsPeople
            WHERE EID = @EID AND  GroupID = @GroupID AND PeopleID IN
                  ( SELECT DISTINCT PeopleID
                        FROM NPMSPeople
                        WHERE EID = @EID AND FamilyID = @FamilyID )

When I run this query I get the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

How do I reconstruct this query to make it work?


0
Comment
Question by:rgrimm
15 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11802257
Change IN to EXISTS.
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11802263
The IN term requires a subquery to return exactly one row. EXISTS accepts any number of rows, which is what you need for this query. To use EXISTS, you need to move PeopleID to the WHERE clause of the subquery and join it to the outer query:

          DELETE FROM GroupsPeople
          WHERE EID = @EID AND  GroupID = @GroupID AND EXISTS
               ( SELECT DISTINCT PeopleID
                    FROM NPMSPeople
                    WHERE PeopleID = GroupsPeople.PeopleID AND EID = @EID AND FamilyID = @FamilyID )
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11802302
Well, I was totally wrong. IN and EXISTS can both accept multiple records from a subquery. From my testing, the problem appears to be from using the variable in both the outer query and the subquery. This restructuring works for me:

DELETE FROM GroupsPeople
WHERE EID = @EID AND  GroupID = @GroupID AND PeopleID IN
 (SELECT DISTINCT PeopleID
  FROM NPMSPeople
  WHERE PeopleID = GroupsPeople.PeopleID
   AND EID = GroupsPeople.EID
   AND FamilyID = GroupsPeople.FamilyID )
0
 

Author Comment

by:rgrimm
ID: 11802364
Unfortunately the table GroupsPeople does not contain a FamilyID column, so the last line is invalid.
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11802391
Ah, then that's probably the only thing wrong with your original query, and the error message was misleading. Try this:

          DELETE FROM GroupsPeople
          WHERE EID = @EID AND  GroupID = @GroupID AND PeopleID IN
               ( SELECT DISTINCT PeopleID
                    FROM NPMSPeople
                    WHERE EID = @EID )
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11802411
Yeah, that last was my stupidest yet. I should probably quit trying to "help", but here's yet one more try:

DELETE FROM GroupsPeople
WHERE EID = @EID AND  GroupID = @GroupID AND PeopleID IN
 (SELECT DISTINCT PeopleID
  FROM NPMSPeople
  WHERE PeopleID = GroupsPeople.PeopleID
   AND EID = GroupsPeople.EID
   AND FamilyID = @FamilyID )

0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11802451
My only redeeming value (if any) is that I'm online (cause I'm in bed with a bad back) and willing to *try* to help... Sorry for the bad advice.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:rgrimm
ID: 11802594
Hate to say it, but I get the same error. I don't understand why it cannot work with a set of data from a subquery to do this. Working with subqueries should be second nature. If I take your last query and substitute SELECT * instead of DELETE it works fine. But DELETE and subqueries don't seem to mix.

If your bad back gives you any more ideas send them my way. I'll keep researching too.

Thanks!
0
 

Author Comment

by:rgrimm
ID: 11802702
I'm reading in BOL about cursors as a possible solution, but I can't find any example code yet. Any ideas?
0
 
LVL 15

Accepted Solution

by:
jdlambert1 earned 400 total points
ID: 11802745
I may be nuts from the pain meds, but I'm gonna take another crack at this. The DELETE structure you have uses a SQL-92 standard subquery. I don't know why it's not working, but here's a T-SQL alternate structure:

DELETE GroupsPeople
FROM GroupsPeople INNER JOIN NPMSPeople
 ON GroupsPeople.PeopleID = NPMSPeople.PeopleID
WHERE GroupsPeople.EID = @EID
 AND GroupsPeople.GroupID = @GroupID
 AND NPMSPeople.FamilyID = @FamilyID

Oh, I don't see NPMSPeople in your list of table structures, but if it's like the People structure, People doesn't have an EID column, but your original DELETE subquery tries to refer to one.
0
 
LVL 34

Expert Comment

by:flavo
ID: 11802768
DELETE FROM GroupsPeople
WHERE EXISTS (
SELECT DISTINCT PeopleID
  FROM GroupsPeople NPMSPeople
  WHERE NPMSPeople.PeopleID = GroupsPeople.PeopleID
   AND NPMSPeople.EID = GroupsPeople.EID
   AND FamilyID = @FamilyID
)
and EID = @EID AND  GroupID = @GroupID

maybe...
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11802776
You shouldn't need a cursor for this, and cursors always have worse performance than set-based operations. If you want to try it anyway, you can get a template for cursors (and lots of other common, time-saving templates) like this: Open Query Analyzer, click Tools/Object Browser, then Show/Hide (or press F8), then click the Templates tab at the bottom. Then expand Using Cursor, and drag one of the items onto the main work area, and it will pop in a whole template.
0
 
LVL 12

Assisted Solution

by:kselvia
kselvia earned 100 total points
ID: 11803442
What is NPMSPeople? It's not the name of a table you described. Is it a view? What does that view look like? Does it contain a subquery?    If not, is there a delete trigger on GroupsPeople?  The problem must lie elsewhere because the subquery you have is fine.
0
 

Author Comment

by:rgrimm
ID: 11804222
Here is the non-cursor solution in its simplest form:

DELETE
FROM GroupsPeople
WHERE PeopleID IN ( SELECT PeopleID FROM People WHERE FamilyID = @FamilyID)

KSelvia's idea pinpointed the source of the problem, which was in a delete trigger on GroupsPeople. Once that was resolved I was able to test solutions without getting adverse effects.
0
 

Author Comment

by:rgrimm
ID: 11805527
For readers of this solution:

The ultimate problem was not in the SQL listed in this message thread, but was in a delete trigger that was being fired. The code samples listed above (8/15/2004 8:17AM, and 8/14/2004 8:06PM) both do the job, as does the cursor-based solution shown below, although cursors perform more slowly. My ultimate preference is for the code shown in the 8/15/2004 8:17AM comment for its conciseness (many thanks to M. Eschenburg in DC!).

I have now looked at a lot of web sources on this subject, and this error is a common problem. The solutions here work, so if you can't seem to get away from the error, start looking for a cause in a trigger.

Rick


            DECLARE @PeopleID int
            
            DECLARE PeopleIDCursor cursor
            FOR
            SELECT DISTINCT PeopleID
            FROM NPMSPeople
            WHERE EID = @EID AND FamilyID = @FamilyID
            
            OPEN PeopleIDCursor
            FETCH NEXT FROM PeopleIDCursor into @PeopleID
            WHILE (@@fetch_status <> -1 )
            BEGIN
                  DELETE FROM GroupsPeople
                  WHERE EID = @EID AND GroupID = @GroupID AND PeopleID = @PeopleID
                  FETCH NEXT FROM PeopleIDCursor into @PeopleID
            END
            CLOSE PeopleIDCursor
            DEALLOCATE PeopleIDCursor
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Database tuning – How to start and what to tune. This question is frequently asked by many people, both online and offline. There is no hard and fast rule-of-thumb for performance tuning, however, before beginning the tuning process one should a…
I guess that all of us know that caching the data usually increase the performance, but I worried if all of us are aware about the risk that caching the data provides and how to minimize this.  That’s the reason why I decided to write this short art…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now