What causes the error "Subquery returned more than one value ..."?

Question

I have no idea why I am 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 tried 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 = ' [e-mail] ' 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

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

Personally, I find it very bad practice to disable check restrictions when performing inserts or updates. They exist for some reason, and if your data does not meet these restrictions, they should not be inserted as is or the restriction needs to be adjusted for new conditions. It can also cause problems if records are updated in the future.

Answer 2

It looks like you need a semicolon to complete 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 = ' [e-mail] ' WHERE RefID = '05e6c6b4-63ff-45b2-a5e2-920d5dce3e45' ; <-- semicolon