Subquery returned more than 1 value. Explanation would be appreciated

Question

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I know most of you have probably seen this error a hundred times and a short answer to it would be change the operator = to in, however, that doesn't seem to work with me.

UPDATE _RS
SET _RS.GroupID = (SELECT ID FROM _RefSkillGroup WHERE Code like '%SKILL_GODBLESS_%')
FROM _RefSkill _RS
JOIN _RefSkillGroup _RSG 
ON _RS.GroupID=_RSG.ID
WHERE _RS.ID BETWEEN 33816 AND 33824

UPDATE _RefSkill 
SET GroupID = (SELECT ID FROM _RefSkillGroup WHERE Code LIKE '%SKILL_GODBLESS_%')
WHERE ID BETWEEN 33816 AND 33824

That is in _RefSkill:

Service ID GroupID Basic_Code 
1 33816 1027 SKILL_GODBLESS_FIRE
1 33817 1028 SKILL_GODBLESS_RED
1 33818 1029 SKILL_GODBLESS_BLUE
1 33819 1030 SKILL_GODBLESS_WHIT

And that is in _RefSkillGroup

ID Code
1038 SKILL_GODBLESS_FIRE
1039 SKILL_GODBLESS_RED
1040 SKILL_GODBLESS_BLUE
1041 SKILL_GODBLESS_WHIT

The error message says it all. You want to assign a value to your column, but the subqeury returns several values. Which one do you want? — jarlh, Jun 08 '20 at 07:32
Thank you Jarlh. I'm trying to update a value in different rows. That is in _RefSkill: Service ID GroupID Basic_Code 1 33816 1027 SKILL_GODBLESS_FIRE 1 33817 1028 SKILL_GODBLESS_RED 1 33818 1029 SKILL_GODBLESS_BLUE 1 33819 1030 SKILL_GODBLESS_WHIT And that is in _RefSkillGroup ID Code 1038 SKILL_GODBLESS_FIRE 1039 SKILL_GODBLESS_RED 1040 SKILL_GODBLESS_BLUE 1041 SKILL_GODBLESS_WHIT What I'm trying to do is to correct the value in the GroupID column in _RefSkill, not for one row but for all the rows following the condition 'SKILL_GODBLESS_%' — SroMax, Jun 08 '20 at 07:41
The view is messed. Here is a screenshot of my reply http://prntscr.com/svqlsy — SroMax, Jun 08 '20 at 07:42
`SELECT ID FROM _RefSkillGroup WHERE Code like '%SKILL_GODBLESS_%'` returns more than one row. — Zohar Peled, Jun 08 '20 at 07:53
Please avoid using the comments to post code (well, at least, long code). Instead, [edit] your question to provide the data. Also, please read and follow the instructions on the [sql tag info](https://stackoverflow.com/tags/sql/info) on how to write a good SQL question. — Zohar Peled, Jun 08 '20 at 07:56
Noted. Thank you. I know the query returns more than one row. I want to update the value in all the rows matching that condition, such a thing isn't possible with this syntax? — SroMax, Jun 08 '20 at 07:59
Please state in plain English, what you want, specifically: what column in what table should be updated, what value should be set there and based on what condition. Expected output would be helpful. — Alex, Jun 08 '20 at 08:33
Right. What I want to do is to update the column GroupID in _RefSkill with the value of ID column in _RefSkillGroup given that the Basic_Code in _RefSkill is the same as Code in _RefSkillGroup. I hope that is clear to understand :( @Alex — SroMax, Jun 08 '20 at 08:42

Alex · Accepted Answer · 2020-06-08T10:08:09.503

0

Thanks for clarifying your requirement.

The query is below:

UPDATE _RS
-- "update the column GroupID in _RefSkill with the value of ID"
SET _RS.GroupID = _RSG.ID
FROM _RefSkill _RS
    -- You join on a table where you want to look up a value.
    -- "given that the Basic_Code in _RefSkill is the same as Code in _RefSkillGroup"
    JOIN _RefSkillGroup _RSG ON _RS.Basic_Code =_RSG.Code
WHERE _RS.ID BETWEEN 33816 AND 33824

Other comments: I would suggest that you not prefix you table/column names with underscores.

Update:

This SO question deals with various update join formats

edited Jun 08 '20 at 10:08

answered Jun 08 '20 at 08:49

Alex

4,885
3
19
39

Thank you so much Alex! It worked and the explanation really helps me alot! Is there a way to do that without using the joins? – SroMax Jun 08 '20 at 09:53
No, as you need to copy values from one table to another, you have to join two tables. There are different join formats available though. – Alex Jun 08 '20 at 09:59

score 0 · Answer 2 · answered Jun 08 '20 at 12:00

Your code would be fine with a correlation clause instead of a join:

UPDATE RS
    SET GroupID = (SELECT rsg.ID
                   FROM _RefSkillGroup rsg
                    WHERE rsg.Code LIKE '%SKILL_GODBLESS_%' AND
                          rs.GroupID = rsg.ID
                  )
    FROM _RefSkill RS
    WHERE RS.ID BETWEEN 33816 AND 33824;

This assumes that the subquery does, indeed, only return one row for each row in RS. If not, you need to figure out which of these rows you want.

Subquery returned more than 1 value. Explanation would be appreciated

2 Answers2