Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2004-08-14
15
Medium Priority
?
575 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

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
 

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 1600 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 400 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
What we learned in Webroot's webinar on multi-vector protection.
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

670 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