想一想就明白了啊?
如果用了 LEFT JOIN ,如果有多条记录符合条件时,你要系统用哪条记录更新呢?
-------------------------------
问题:
想达到此目的,如何修改下面的update语句
update usertabb094 t1 ,(select field_3, sum(field_9) as field_91,sum(field_10 ) as field_101 from usertabb095 group by field_3 ) t2 set t1.field_8=(t1.field_8*t1.field_7+t2.field_101)/(t1.field_7+t2.field_91) where t1.field_1=t2.field_3
上面的语句运行显示:
操作必需使用一个可更新的查询
回答:
必须拆成2个查询才能运行
1、select field_3, sum(field_9) as field_91,sum(field_10 ) as field_101 into [095] from usertabb095 group by field_3
注意:使用套嵌查询就会出现你说的问题,但是使用临时表没问题
2、UPDATE usertabb094 AS t1 INNER JOIN 095 ON t1.field_1=[095].field_3 SET t1.field_8 = (t1.field_8*t1.field_7+[095].field_101)/(t1.field_7+[095].field_91);