What is causing "Subquery returned more than 1 value..." error?

Question

Hi I dont have any idea why am i getting this 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.

I was trying to run this query:-

ALTER TABLE Test1 NOCHECK CONSTRAINT ALL
ALTER TABLE Test2 NOCHECK CONSTRAINT ALL


UPDATE Test1 
SET ID= '05f6c6b4-63ff-45b2-a5e2-920d5dce3e45' 
WHERE ID = '05e6c6b4-63ff-45b2-a5e2-920d5dce3e45'; 
UPDATE Test2 
SET ID = '05f6c6b4-63ff-45b2-a5e2-920d5dce3e45' ,
SpecID = NULL ,
RefLastName = 'Dummy' ,
RefFirstName = 'First Name' ,
RefMiddleName = NULL ,
RefPhone1 = '111444444' ,
RefPhone2 = '2121222' ,
RefFax = '222222' ,
RefEmail = '[email protected]' 
WHERE 
RefID = '05e6c6b4-63ff-45b2-a5e2-920d5dce3e45' 

ALTER TABLE Test1 WITH CHECK CHECK CONSTRAINT ALL
ALTER TABLE Test2 WITH CHECK CHECK CONSTRAINT ALL

Answer 1

Looks like you need a semicolon to end this statement:

 UPDATE Test  
 SET RefID = '05f6c6b4-63ff-45b2-a5e2-920d5dce3e45' 
 WHERE RefID = '05e6c6b4-63ff-45b2-a5e2-920d5dce3e45'; 

 UPDATE RefPhysician 
 SET RefID = '05f6c6b4-63ff-45b2-a5e2-920d5dce3e45' ,
 SpecID = NULL ,
 RefLastName = 'DR. BRAD DIBBLE' ,
 RefFirstName = 'First Name' ,
 RefMiddleName = NULL ,
 RefPhone1 = '613444444' ,
 RefPhone2 = 'print,read,866,1' ,
 RefFax = '6476476464' ,
 RefEmail = '[email protected]' 
 WHERE RefID = '05e6c6b4-63ff-45b2-a5e2-920d5dce3e45'  ; <-- semicolon

Answer 2

Does one of the tables you are updating have a trigger? If it does, likely it contains a subquery with more than one value.

Personally I find it a very bad practice to turn check constraints off when doing inserts or updates. They are there for a reason and if your data doesn't meet those constraints, it should not be inserted as is or the constraint needs to be adjusted for new conditions. It can also create problems if the records is updated in the future.