現在有T1(tid,tdate,tmoney)和T2(sid,sdate,edate)兩張表,結構如下
表T1 | 表T2
tid tdate tmoney | sid sdate edate SS
001 2007/07/01 100 | 001 2007/07/01 2007/07/01 20
001 2007/07/02 80 | 001 2007/07/05 2007/07/06 5
.. .. .. | .. .. .. ..
002 2007/07/01 75 | 002 2007/07/08 2007/07/10 45
002 2007/07/02 25 | 003 2007/07/02 2007/07/05 10
.. .. .. | .. .. ..現在我想修改表T1的tmoney字段
如要T1.tmoney>100-T2.SS 則取100-T2.SS 否則就保持T1.tmoney值不變
條件是T1表中的tid要等於T2表中的sid,並且T1表中的tdate要在T2表的sdate和edate之間.請問應該如何寫成SQL詞句.
set tmoney=case when T1.tmoney>100-T2.SS then 100-T2.SS else T1.tmoney end
from T1,T2
where T1.tid =T2.Sid
and T1.tdate between T2.sdate and T2.edate
set tmoney = case when T1.tmoney > 100 - T2.SS then 100 - T2.SS else T1.money end
from T1, T2
where T1.tid = T2.sid and (T1.tdate between T2.sdate and T2.edate)
T1
Set
tmoney = (Case When T1.tmoney>100-T2.SS Then 100-T2.SS Else T1.tmoney End)
From
T1
Inner Join
T2
On T1.tid = T2.sid And T1.tdate Between T2.sdate And T2.edate
如果T1表的Tdate還要在一時間段內,例如'2007/06/01'到'2007/06/30',是否還可以加上
T1.tdate between '2007/06/01' and '2007/06/30' ????
謝謝
先測試看看,回來加分。
如果T1表的Tdate還要在一時間段內,例如'2007/06/01'到'2007/06/30',是否還可以加上
T1.tdate between '2007/06/01' and '2007/06/30' ????
謝謝
------------
可以
Update
T1
Set
tmoney = (Case When T1.tmoney>100-T2.SS Then 100-T2.SS Else T1.tmoney End)
From
T1
Inner Join
T2
On T1.tid = T2.sid And T1.tdate Between T2.sdate And T2.edate
And T1.tdate between '2007/06/01' and '2007/06/30'
T1
Inner Join
T2
On T1.tid = T2.sid And T1.tdate Between T2.sdate And T2.edate
And T1.tdate between '2007/06/01' and '2007/06/30'
select From T1
Where exists (
select 1 from T2
where T1.tid = T2.sid
And T1.tdate Between T2.bdate And T2.edate
) and T1.tdate between '2007/06/01' and '2007/06/30'上面兩條語句有什麼不同碼,得到的記錄數不一樣??
T1
Inner Join
T2
On T1.tid = T2.sid And T1.tdate Between T2.sdate And T2.edate
And T1.tdate between '2007/06/01' and '2007/06/30'
但是要改成UPDATE的時候能加Distinct嗎???
應該怎麼寫呢??