update Emp set money='100' where money=''
update Emp set money='100' where money<'1'
update Emp set Dj='1' where Dj=''
-----如果money为空的话充100元
-----如果小于1的话充100元
-----如果Dj为空的话更新为1
以上怎么解决,求高手回答,谢谢
------解决方案--------------------
- SQL code
declare @EMP table (ID int,Dj varchar(2),money varchar(8))insert into @EMPselect 1,'','' union allselect 2,'2','10' union allselect 3,'3','100' union allselect 4,'4','0' union allselect 5,'5','80'--原数据select * from @EMP/*ID Dj money----------- ---- --------1 2 2 103 3 1004 4 05 5 80*/UPDATE @EMP SET DJ=CASE WHEN DJ='' THEN '1' ELSE DJ END,MONEY=(CASE WHEN MONEY+0<1 and MONEY<>'' THEN CONVERT(VARCHAR(10),MONEY+80) WHEN MONEY='' THEN CONVERT(VARCHAR(10),MONEY+100) ELSE MONEY END)--更新后SELECT * FROM @EMP/*ID Dj money----------- ---- --------1 1 1002 2 103 3 1004 4 805 5 80*/
------解决方案--------------------
叶子正解!
------解决方案--------------------
to maco_wang:
money为0的时候为什么是冲了80?
------解决方案--------------------
一楼正解!!!
------解决方案--------------------
- SQL code
declare @EMP table (ID int,Dj varchar(2),money varchar(8))insert into @EMPselect 1,'','' union allselect 2,'2','10' union allselect 3,'3','100' union allselect 4,'4','0' union allselect 5,'5','80'UPDATE @EMPSET [money] = CASE WHEN LEN(ISNULL([money],'')) = 0 OR [money] < 1 THEN 100 ELSE [money] END, Dj = CASE WHEN LEN(ISNULL(Dj,'')) = 0 THEN '1' ELSE Dj END; SELECT * FROM @EMP;ID Dj money----------- ---- --------1 1 1002 2 103 3 1004 4 1005 5 80(5 row(s) affected)
------解决方案--------------------