Update Multiple using Select - Subquery returned more than 1 value

_{Wednesday, June 8, 2011 1:44 PM}

Hello,

I'm trying to mass update a table using values from another table. However I keep getting error message:

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

This is the query that I wrote:

UPDATE Student
SET ClassEnrolledCode = (SELECT b.NewClassCode FROM Student a INNER JOIN Class b on a.ClassId = b.ClassId
WHERE ClassCategory = 180301243 AND a.ClassEnrolledCode =b.OldClassCode)

Could anyone please help me on this one? Greatly appreciate your help!

-Elisabeth

_{Wednesday, June 8, 2011 1:52 PM}

UPDATE a
SET a.ClassEnrolledCode =b.NewClassCode
FROM Student a INNER JOIN Class b on a.ClassId = b.ClassId AND a.ClassEnrolledCode =b.OldClassCode
WHERE ClassCategory = 180301243 

Yogesh Bhadauriya
My Blog

_{Wednesday, June 8, 2011 1:54 PM}

The error is clear, the subquery is returning multiple rows, so you have to decide which one to use for the update. You can use TOP and ORDER BY to pickup one.

UPDATE Student
SET ClassEnrolledCode = (
SELECT TOP (1) b.NewClassCode
FROM Student a INNER JOIN Class b on a.ClassId = b.ClassId
WHERE ClassCategory = 180301243 AND a.ClassEnrolledCode =b.OldClassCode
ORDER BY b.NewClassCode
);

The problem could be in the model, since it is not common to get multiple values to update specific row / column. May be you need to adjust the joining conditions or filter.

UPDATE Student
SET ClassEnrolledCode = (
    SELECT TOP (1) b.NewClassCode
    FROM Class b on b.ClassId = Student.ClassId
    WHERE b.ClassCategory = 180301243 AND b.OldClassCode =  Student.OldClassCode
    ORDER BY b.NewClassCode
    )
WHERE EXISTS (
select *
from Class b on b.ClassId = Student.ClassId
WHERE b.ClassCategory = 180301243 AND b.OldClassCode =  Student.OldClassCode
);

or you can use:

update A
set A.ClassEnrolledCode = B.NewClassCode
from student as A inner join Class as B on A.ClassId = B.ClassId
where B.ClassCategory = 180301243 AND A.OldClassCode =  B.OldClassCode;

AMB

Some guidelines for posting questions...

_{Wednesday, June 8, 2011 2:06 PM}

Hunchback and Yogesh, thanks for your reply.

I tried both of you guys' queries, but then I got a new error message:

Violation of PRIMARY KEY constraint 'pkxPropPMSChg'. Cannot insert duplicate key in object 'dbo.Student'

I'm not sure about this PRIMARY KEY constraint 'pkxPropPMSChg'.. it's not even a column in the Student table.

Could you please help me on this one?

Thanks!

-ELisabeth

_{Wednesday, June 8, 2011 2:31 PM}

Could you please proved the tables structure?Muhammad Azeem

_{Wednesday, June 8, 2011 2:37 PM}

Muhammad, thanks for your reply.

Did sp_help on the table, and the constraint type for the table is:

constraint_type                  constraint_name     constraint_keys
PRIMARY KEY (clustered)    pkxPropPMSChg      ClassCategory, ChgBeginDt, ClassEnrolledCode

Which is weird because I'm not inserting any duplicate keys based on this three fields: ClassCategory, ChgBeginDt, ClassEnrolledCode.... even weirder because I'm not inserting but updating the table..

Could someone please clarify this? Thanks in advanced for all your help.

-Elisabeth

_{Wednesday, June 8, 2011 3:25 PM}

Try something like this to get you close to finding the values involved in the duplicates

WITH CTEDupFind AS
(
SELECT C.NewClassCode, S.ClassCategory, S.ChgBeginDt
FROM Student S
JOIN Class C
    ON S.ClassID = C.ClassID
    AND S.ClassEnrolledCode = C.OldClassCode
WHERE ClassCategory = 180301243

UNION ALL

SELECT S.ClassEnrolledCode , S.ClassCategory, S.ChgBeginDt
FROM Student S
--JOIN Class C
--   ON S.ClassID <> C.ClassID
--   AND S.ClassEnrolledCode <> C.OldClassCode
WHERE ClassCategory <> 180301243
)



SELECT NewClassCode, ClassCategory, ChgBeginDt
FROM CTEDupFind
GROUP BY NewClassCode, ClassCategory, ChgBeginDt
HAVING COUNT(*) > 1

If there was a problem - Yo, I'll solve it

_{Wednesday, June 8, 2011 3:27 PM}

Muhammad, thanks for your reply.

Did sp_help on the table, and the constraint type for the table is:

constraint_type                  constraint_name     constraint_keys
PRIMARY KEY (clustered)    pkxPropPMSChg      ClassCategory, ChgBeginDt, ClassEnrolledCode

 

Which is weird because I'm not inserting any duplicate keys based on this three fields: ClassCategory, ChgBeginDt, ClassEnrolledCode.... even weirder because I'm not inserting but updating the table..

Could someone please clarify this? Thanks in advanced for all your help.

 

-Elisabeth

Hi,

As a primary key issue I am sure your retrieving date is duplicated. I am creating a scenario for your understanding ...

Fields:

ClassEnrolledCode , ClassCategory, ChgBeginDt

Row 1: 1 , 1, 1 accepted input

Row 2: 1 , 2, 2 accepted input

Row 3: 1 , 1, 2 accepted input

Row 4: 1 , 1, 1 duplicate input

Please check your all three fields (ClassEnrolledCode , ClassCategory, ChgBeginDt) in STUDENT table ... I am sure they have same values ...

Thanks,

Muhammad Azeem

_{Wednesday, June 8, 2011 10:58 PM}

>> I'm trying to mass update a table using values from another table. <<

That is a good sign that your schema is not normalized and you have split data over multiple tables. Not proof, just a smell that you will recognize after a few decades with RDBMS.

>> However I keep getting error message:

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

Yep!  That is why good SQL programmer never, never use this proprietary UPDATE.. FROM syntax. When it was introduced back in the old Sybase days, it would randomly pick one of the multiple candidates, based on the physical location and indexing of the tables. This error message is a blessing.

I am sorry to see you have only one Student, as your table name says. I also loved those alphabetical aliases; why do you think that approach will help maintain your code? Would calling them S and C be more helpful?

I had to guess that class_category is in Classes. But do you really use HUGE integers like 180301243?  Hundred of millions of courses is a big university. We used to get category data from the prefixes on the course numbers  “MATH-101” was a math course, etc.

UPDATE Students
  SET class_enrollment_code
       = (SELECT C.new_class_code
            FROM Students AS S, Classes AS C
           WHERE S.class_id = C.class_id
             AND C.class_category = 180301243
             AND S.class_enrollment_code = C.old_class_code)

You do know that if the subquery is empty, it becomes a NULL. Is that what you wanted?

I am going to guess that the DDL you did not post (very rude Netiquette) is wrong and that you wanted a look up table to change an old code to a new one. Constraints would prevent duplicates like you got:

CREATE TABLE Lookup_Class_Codes
(old_class_code CHAR(10) NOT NULL UNIQUE,
 new_class_code CHAR(10) NOT NULL UNIQUE,
 class_category BIGINT NOT NULL
 etc.);

Of course this data model is completely wrong. A class is not an attribute of a student any more than my books are attributes of me. We are entities that have a relationship called “authorship”; you should have an “Enrollment” or “Class_Roster” relationship.
--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL