Getting "Subquery returns more than one value" query when starting an update
Please be careful, I am trying to update a query on sql server but encountering an error. Here are my two tables that are in the same database and below is the query and my requirement is to update the groupCode column in table2 based on table1, but I ran into the following error:
Mistake
Msg 512, Level 16, State 1, Line 1 Subquery returns more than 1 cost. This is not allowed when the subquery follows = ,! =, <, <=,>,> = or when a subquery is used as an expression. The application has been completed.
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
)
)
source to share
The error message indicates that one or both of the subqueries returned multiple rows. This is not allowed because you are using subqueries as operand =
. One possible way to fix the error is to add TOP 1
to each of your subqueries.
Another possible way to solve this update problem is to use the UPDATE ... FROM ... JOIN
syntax:
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
source to share
the error just tells you your inner query is returning more than one value so sql get confused.so prevent multiple value using top cluase
try it.
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))
source to share
You should always update so that the inner selection always returns a single row. Your example doesn't really match 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 make sense:
Groupname =(select activity from db1..Activity where DeptCode=32))
Since you are trying to update the table from yourself, if this is indeed the case, then you can simply use:
update table2 set GroupName = Activity
without using any internal samples.
source to share