比如有下面两条update操作
update info set TODAY_FEE=TODAY_FEE+100 , TODAY_FEE_NUM=TODAY_FEE_NUM+1 where DESTMSISDN='13911111111' and FEE_DATE=sysdate
update info set TODAY_FEE=100,TODAY_FEE_NUM=1,FEE_DATE=sysdate where DESTMSISDN='13911111111' and FEE_DATE=sysdate-1主要由于条件不同,update的赋值不同,请问如何能用一条sql写了,感谢!
update info set TODAY_FEE=TODAY_FEE+100 , TODAY_FEE_NUM=TODAY_FEE_NUM+1 where DESTMSISDN='13911111111' and FEE_DATE=sysdate
update info set TODAY_FEE=100,TODAY_FEE_NUM=1,FEE_DATE=sysdate where DESTMSISDN='13911111111' and FEE_DATE=sysdate-1主要由于条件不同,update的赋值不同,请问如何能用一条sql写了,感谢!
TODAY_FEE_NUM=decode(FEE_DATE,sysdate,TODAY_FEE_NUM+1,1)
where DESTMSISDN='13911111111' and FEE_DATE in (sysdate-1,sysdate);
create table FOX1
(
WORKNO VARCHAR2(8),
NAME VARCHAR2(8),
AGE NUMBER
);--
1 11 11 1
2 22 22 1
3 33 33 1
4 44 44 1
5 55 55 1
6 66 66 1
7 77 77 1
8 6 3 3update fox1
set age = case when workno - name <0 then 0
when workno - name =0 then 1
else workno - name
end
update info set TODAY_FEE=decode(FEE_DATE,sysdate,TODAY_FEE+100 ,sysdate-1,100),
TODAY_FEE_NUM=decode(FEE_DATE,sysdate,TODAY_FEE_NUM+1 ,sysdate-1,100),
FEE_DATE=decode(FEE_DATE,sysdate,sysdate,sysdate-1,sysdate) where DESTMSISDN='13911111111'
set
(TODAY_FEE,TODAY_FEE_NUM) =
(select TODAY_FEE+100,TODAY_FEE_NUM+1
from dual
where FEE_DATE=sysdate
union
select 100,1
from dual
where FEE_DATE= sysdate-1
)
where 1=1
and DESTMSISDN='13911111111'
and FEE_DATE in (sysdate-1,sysdate);顺便说一下,sysdate 返回的是值相当于实数,不太清楚你的用意,如果是表示当天或上一天的话应该是trunc(FEE_DATE)=trunc(sysdate) 或trunc(FEE_DATE)=trunc(sysdate)-1