Update column with subquery

Question

0.00/5 (No votes)

See more:

I have to table one is called category and have 2 columns type and Numberofmovies and another table called Movtype have the type and the movieID and i want update the Numberofmovies column the count number of those movies and this is my code but it gives me that error :

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

What I have tried:

update Category
set MovieNumbers = (select count(MT.MovieID)
from MovType MT inner join Category C
on  MT.[type] = C.[Type]
group by C.[Type])

Posted 19-Jan-17 11:56am

Hussien Ezzat

Updated 19-Jan-17 13:16pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RossMW · Answer 1 · 2017-01-19T13:16:00

Solution 1

Try running the

select count(MT.MovieID)
from MovType MT inner join Category C
on  MT.[type] = C.[Type]
group by C.[Type])

part of your statement sparately. You will probably find it is returning mulltiple records and hence the set MovieNumber = statement will fail with your error as it can not update it to multiple values.

Also you will need a where clause at the end so only the appropiate record is updated.

Try

update Category 
set MovieNumbers = numb 

from 

(select count(MovieID) as Numb, Type from MovType group by Type) x
where x.type = category.type

Posted 19-Jan-17 13:16pm

RossMW

Updated 22-Jan-17 8:19am

v4

Comments

Hussien Ezzat 20-Jan-17 4:13am

Yes i know that it will retrieve multiple values ,, what am asking is is there any way to update the whole Column of MovieNumbers with this multiple returned values at once ??

RossMW 22-Jan-17 14:17pm

If you look closely at your statement you have
update Category set MovieNumbers = x

Your statement does not contain any where clause so it is trying to update every record with multiple values returned by your
select count(MT.MovieID)
from MovType MT inner join Category C
on MT.[type] = C.[Type]
group by C.[Type]
statement and hence the error

Try
update Category
set MovieNumbers = numb

from

(select count(MovieID) as Numb, Type from MovType group by Type) x
where x.type = category.type

I will update the solution...