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
                                    )
             )

Answer 1

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