sql update 特殊用法

转载自：
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
1
2
3
4
5

2.Count更新

schoolbasicinfo 中存在 MENBERCOUNT 字段记录条数 vw_shcoolbasic 做Count查询

update schoolbasicinfo set MENBERCOUNT=
(select vw_shcoolbasic.countnum from vw_shcoolbasic where schoolbasicinfo.schoolinfoid=vw_shcoolbasic.schoolid)
1
2

在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
)
1
2
3
4
5
6
7
8
9
10
11