如下SQL語句,我要把TRDATA中11:30 到12:30 的時間記錄Update到TABATT的F01中,但是,有時TRDATA中11:30 到12:30 的記錄有多條時,更新的記錄好象是任意的,我如何更新第一條呢?如有三條" 1131,1200,1210"時就更新1131這個記錄. 請條們大大幫手.謝謝.

 
Update TabAtt Set F01 = R.TRTime
From TabAtt A, TRData T
Where A.Emp_Code = T.Emp_Code and A.TRDate=T.TRDate
and T.TRTime Between '1130' and '1230'

發表人 - martintop 於 2005/01/21 21:20:35

各位大大,這種情況的UPDATE語句真的沒法解決嗎? 傷心ing.

您好﹗    Update TabAtt A, (Select Min(TRTime) From TRData Where TRTime Between '1130' and '1230' Order By Emp_Code) T Set A.F01=T.TRTime Where A.Emp_Code=T.Emp_Code and A.TRDate=T.TRDate    ================================= 有空來瞅瞅我﹗因為我是您的朋友﹐有您真好﹗ ================================

------
忻晟

還有這種寫法呀,先謝謝版主,我試試.

引言: 您好﹗ Update TabAtt A, (Select Min(TRTime) From TRData Where TRTime Between '1130' and '1230' Order By Emp_Code) T Set A.F01=T.TRTime Where A.Emp_Code=T.Emp_Code and A.TRDate=T.TRDate ================================= 有空來瞅瞅我﹗因為我是您的朋友﹐有您真好﹗ ================================

功力不夠呀,我怎麼就畫不出這個瓢來呢,慚愧. 麻煩 class="code"> Update Pattend Set F_Out =R.TRTime From Pattend P,Pattend_Record R,Prank_Area A Where (P.Rank_Type = A.Rank_Code)and (P.Emp_Code = R.Emp_Code) and (isnull(P.F_Out,'')='') and (convert(datetime,P.Attend_DAte )=convert(datetime,R.TRDate)) and(P.Attend_Date between @Min_Date and @Max_Date) and (R.TRTime between A.F_Out0 and A.F_Out1) and(isnull(P.F_In,'') <> R.TRTime)and ( A.Over_Dot not between 2 and 3) 發表人 - martintop 於 2005/01/22 10:44:49

您好﹗    這樣試試﹕ Update Pattend P, Pattend_Record R, (Select Min(R.TRTime) From Pattend_Record R, Prank_Area A Where R.TRTime Between A.F_Out0 and A.F_Out1 Group By R.Emp_Code) A Set F_Out=R.TRTime Where (P.Rank_Type=A.Rank_Code) and (P.Emp_Code=R.Emp_Code) and  (isnull(P.F_Out,'')='') and (convert(datetime, P.Attend_Date )=convert(datetime,R.TRDate)) and (isnull(P.F_In,'') <> R.TRTime) and (A.Over_Dot not between 2 and 3)     ================================= 有空來瞅瞅我﹗因為我是您的朋友﹐有您真好﹗ ================================

------
忻晟

感謝cashxin2002大大回復,但這樣做是不對D,也許你對我的需求還不太清楚,我重新整理了一下,測試通過,下面是我的代碼. Update Pattend set F_Out = R.TRTime From pattend P, (Select R.Emp_Code,R.TRDate,Min(R.TRTime) as TRTime From Pattend_Record R, Prank_Area A, Pattend P Where (P.Emp_Code = R.Emp_Code) and (convert(datetime,P.Attend_DAte )=convert(datetime,R.TRDate)) and( R.TRTime Between A.F_Out0 and A.F_Out1 )and(P.Rank_Type = A.Rank_Code) and(Convert(datetime,R.TRDate) between Convert(datetime,@min_Date) and Convert(datetime,@Max_Date) ) and (A.Over_Dot not between 2 and 3) Group by R.Emp_Code,R.TRDate ) R Where (P.Emp_Code=R.Emp_Code) and (convert(datetime,P.Attend_DAte )=convert(datetime,R.TRDate))and (isnull(P.F_Out,'')='') and (isnull(P.F_In,'') <> R.TRTime) 你那種把查詢結果放在前面的方法我測試通不過,所以放在FROM後面. 再次感謝cashxin2002的幫助. Martin Xiao