使用子查询更新列 [英] Update column with subquery

问题描述

我必须将表1称为类别，并且有2列类型和Numberofmovies，另一个名为Movtype的表具有类型和movieID，我想更新Numberofmovies列这些电影的计数，这是我的代码，但它给出了我错误：

子查询返回的值超过1。当子查询遵循=，！=，<，< =，>，> =或子查询用作表达式时，不允许这样做。

我的尝试：

更新类别
 set MovieNumbers =（选择计数（MT.MovieID）
来自MovType MT内连接类别C 
 on MT。[type] = C. [Type] 
 group by C. [Type]）

解决方案

尝试从MovType MT内部连接类别C运行

选择计数（MT.MovieID）
 
 on MT。[type] = C. [Type] 
 group by C. [Type]）

语句的一部分。您可能会发现它正在返回多个记录，因此设置的MovieNumber =语句将因您的错误而失败，因为它无法将其更新为多个值。



此外，您将最后需要一个where子句，所以只更新适当的记录。



尝试

更新类别
设置MovieNumbers = numb $ b来自
 
的
 $ b（选择计数（MovieID）为Numb，按类型从MovType组输入）x 
其中x.type = category.type 

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

解决方案

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

这篇关于使用子查询更新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！