转载自:
http://www.cnblogs.com/dooom/archive/2010/01/04/1639152.html
- 1.Case更新
- 2.Count更新
1.Case更新
update websitequestion
set hasanswered=case
when hasanswered='n' then '0'
when hasanswered='y' then '1'
else '0' end
2.Count更新
schoolbasicinfo 中存在 MENBERCOUNT 字段记录条数 vw_shcoolbasic 做Count查询
update schoolbasicinfo set MENBERCOUNT=
(select vw_shcoolbasic.countnum from vw_shcoolbasic where schoolbasicinfo.schoolinfoid=vw_shcoolbasic.schoolid)
在count更新的时候,可能schoolbasicinfo.schoolinfoid 不存在于vw_shcoolbasic ,这个时候我们更新schoolbasicinfo想得到的MENBERCOUNT为0,可实际schoolbasicinfo得到的却是 null
可以结合case
update schoolbasicinfo set MENBERCOUNT=
(
case when schoolinfoid not in
(select schoolid from vw_shcoolbasic group by schoolid)
then '0'
else
(select vw_shcoolbasic.countnum
from vw_shcoolbasic
where schoolbasicinfo.schoolinfoid=vw_shcoolbasic.schoolid)
end
)