Getting "Subquery returned more than 1 value" error running update query

Question

Please be gentle, I am trying to update the query in sql server but facing an error. Here is my two tables that are in same database and a query furnished below and my requirement is to update the column groupCode in table2 based on table 1 but I am facing the following error:

Error

Msg 512, Level 16, State 1, Line 1 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.

Table 1

**Dept**    **DeptCode**    **GroupName**   **GroupCode**
IT      32      Login-Els       1
IT      32      QC-Els          4
CT      20      Login-OUP       1
CT      20      XML-OUP         2
CT      20      QC-OUP          4
MECH    34      Login-CEN       1
MECH    34      XML-CEN         2
MECH    34      PAGINATION-CEN  3
MECH    34      QC-CEN          4

Table2

**Activity**    **DeptCode**    **Group**
Login-Els       32      NULL
QC-Els          32      NULL
Login-OUP       20      NULL
XML-OUP         20      NULL
QC-OUP          20      NULL
Login-CEN       34      NULL
XML-CEN         34      NULL
PAGINATION-CEN  34      NULL
QC-CEN          34      NULL

SQL

update db1..Activity set 
Groupcode = (
                select groupcode 
                from db1..Groups 
                where DeptCode=32 
                    and Groupname = (
                                     select activity 
                                     from db1..Activity 
                                     where DeptCode=32
                                    )
             )

WHY and has been there in update code – mohan111 May 31 '15 at 05:24 — mohan111, May 31 '15 at 05:24

score 6 · Accepted Answer · edited May 23 '17 at 12:05

The error message indicates that one or both of your subquery returned more than one row. That's not allowed as you're using the subqueries as operand of =. One possible way to fix the error is by adding TOP 1 to each of your subquery.

Another possible way to accomplish this kind of update task is by using UPDATE ... FROM ... JOIN syntax like so :

UPDATE Activity
SET Groupcode = G.groupcode
FROM Activity A
    INNER JOIN Groups G 
        ON A.activity = G.Groupname
           AND A.DeptCode = G.DeptCode
WHERE A.DeptCode = 32

score 1 · Answer 2 · answered May 31 '15 at 05:24

error simply suggest you that you inner query return more than one value hence sql get confused.so prevent multiple value my using top cluase

try this..

update db1..Activity set 
Groupcode =(select top 1 groupcode from db1..Groups where DeptCode=32 and 
Groupname =(select top 1 activity from db1..Activity where DeptCode=32))

score 0 · Answer 3 · answered May 31 '15 at 05:30

You have to always update so that the inner select always returns one row. Your example doesn't really match with the example tables, but maybe this is what you want:

update
  Table2
set
  Group = (
    select
      GroupCode
    from
      table1
    where
      table1.DeptCode = table2.DeptCode and
      table1.GroupName = table2.Activity
  )

This part doesn't really make sense:

Groupname =(select activity from db1..Activity where DeptCode=32))

Because you're trying to update the table from itself, if that's really the case, then you can just use:

update table2 set GroupName = Activity

without using any inner selects.

Thanks har07, dhaval, jamesZ and all for your great suggestion — Code_Tracer, May 31 '15 at 11:23

Getting "Subquery returned more than 1 value" error running update query

3 Answers3