update 更新数据不正确

[tanyufeng_521]

我有两个表
表一 Lggl_Knowledge_words
字段有：word, Lgbianhao ,DgPhaseID

表二 Lggl_Knowledge_Material
字段有：word、mtbianhao、DgPhaseID,MtID

现在是要求表一中Lgbianhao的值，这个求取这个值的方法为表一中的word对应在表二中word的所有Mtbianhao的平均值

现在我这个更新语句不对，所有的都为一个值去了，求大神帮帮忙！！！

update [Lggl_Knowledge_words]
   set [Lgbianhao] = 
	  isnull(
	  (
	    select sum([Mtbianhao]) / count(*)
		from [Lggl_Knowledge_Material] m
		where m.[word] = [word] and m.[DgPhaseID] = [DgPhaseID]
	  )
	  ,0)
where DgPhaseID = 1

[LongRui888]

试试这个呢：

update [Lggl_Knowledge_words]
   set [Lgbianhao] = m.tt
from [Lggl_Knowledge_words] t
inner join 
(
	select [word],[DgPhaseID],
		   isnull(sum(m.[Mtbianhao]) *1.0/ count(*),0) as tt
	from [Lggl_Knowledge_Material] m
	group by [word],[DgPhaseID]
)m
on m.[word] = t.[word] and 
   m.[DgPhaseID] = t.[DgPhaseID]

--where DgPhaseID = 1

[Neo_whl]

--try this
update [Lggl_Knowledge_words]
   set [Lgbianhao] = 
      isnull(
      (
        select sum([Mtbianhao]) / count(*) --avg([Mtbianhao]) 感觉直接平均值也可以
        from [Lggl_Knowledge_Material] m
        left join  [Lggl_Knowledge_words] on m.[word] =l.[word] and m.[DgPhaseID] =l.[DgPhaseID]
      )
      ,0)  from  Lggl_Knowledge_Material
where DgPhaseID = 1

[tanyufeng_521]

引用 1 楼 yupeigu 的回复:

试试这个呢：

update [Lggl_Knowledge_words]
   set [Lgbianhao] = m.tt
from [Lggl_Knowledge_words] t
inner join 
(
	select [word],[DgPhaseID],
		   isnull(sum(m.[Mtbianhao]) *1.0/ count(*),0) as tt
	from [Lggl_Knowledge_Material] m
	group by [word],[DgPhaseID]
)m
on m.[word] = t.[word] and 
   m.[DgPhaseID] = t.[DgPhaseID]

--where DgPhaseID = 1

你的方法和我的结果是一样的，最后Lgbianhao这个字段的值都是一个数字

[tanyufeng_521]

引用 1 楼 yupeigu 的回复:

试试这个呢：

update [Lggl_Knowledge_words]
   set [Lgbianhao] = m.tt
from [Lggl_Knowledge_words] t
inner join 
(
	select [word],[DgPhaseID],
		   isnull(sum(m.[Mtbianhao]) *1.0/ count(*),0) as tt
	from [Lggl_Knowledge_Material] m
	group by [word],[DgPhaseID]
)m
on m.[word] = t.[word] and 
   m.[DgPhaseID] = t.[DgPhaseID]

--where DgPhaseID = 1

恩，你的方法可以，非常谢谢