Search code examples
sql-server-2008sql-updatecorrelated-subquery

hii, In sql query error is coming in update statement

this is the query I want to update all values of column in challan table i.e msstype in single query

   UPDATE [ProductionDB].[dbo].[Challan]
   SET [MssType] = (select MSSType from TestTable1)
   where ReferenceNo IN (select ReferenceNo  from TestTable);

this error is coming

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.
Solution

  • Maybe:

     UPDATE [ProductionDB].[dbo].[Challan]
 SET [MssType] = (select t.MSSType from TestTable1 t 
                  WHERE t.ReferenceNo  = Challan.ReferenceNo);