iT邦幫忙

2

[AS400/DB2] 如何依查詢出來的欄位去UPDATE曠職時數為0 ?

各位前輩好

我們公司有一台AS400主機,裡面有隻點名程式每天早上8點半會執行
若是前天晚上或者隔天早上網路或者程式異常,就會導致刷卡資料抓不回來
點名程式執行後,就會出現曠職幾小時的情況,在補回刷卡資料後需要下SQL將這些
有刷卡卻曠職的人員找出來,並將曠職時數歸零。

目前有兩個資料表A跟B
A 員工代號、出勤年月、出勤日、曠職時數
B 員工代號、刷卡年月、14日上班時間、14日下班時間

我目前依照如下SQL可以查詢出我要的資料

SELECT 員工代號、出勤年月、出勤日、曠職時數
FROM A
LEFT JOIN B ON A.員工代號 = B.員工代號
WHERE (出勤年月 = '10711' AND 出勤日 = '14' AND 曠職時數 = 4.5) 
AND (刷卡年月 = '10711' AND 14日下班時間 > 1500 ) --確保不會抓到真正曠職或沒刷卡的人

然而,我想加入UPDATE的語法卻失敗,原因是DB2不支援UPDATE...FROM語法,如下:

UPDATE A
SET A.曠職時數 = 0
FROM 
(SELECT 員工代號、出勤年月、出勤日、曠職時數
FROM A
LEFT JOIN B ON A.員工代號 = B.員工代號
WHERE (出勤年月 = '10711' AND 出勤日 = '14' AND 曠職時數 = 4.5) 
AND (刷卡年月 = '10711' AND 14日下班時間 > 1500 )) B
WHERE A.員工代號 = B.員工代號 AND A.出勤年月 = B.出勤年月 AND A.出勤日 = B.出勤日

請問我應該怎麼寫才可以解決我的問題呢?謝謝!

xdxxx iT邦新手 4 級 ‧ 2018-11-23 09:18:52 檢舉
既然你是要update A , 那就用A的條件去找就好了吧
update A set A.曠職時數 = 0 where A.key in (員工代號)
with none

這裡的員工代號應該是主key 唯一值吧? 你只要把 from table 裡的員工代號抓出來不就好了嗎?
b7307024 iT邦新手 3 級 ‧ 2018-11-26 17:43:24 檢舉
你只要把 from table 裡的員工代號抓出來不就好了嗎?
---------- 那請問要怎麼抓呢??
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中
1
rogeryao
iT邦超人 8 級 ‧ 2018-11-16 22:43:29
最佳解答

UPDATE A
SET A.曠職時數 = 0
FROM AXXX as A
INNER JOIN BXXX as B ON A.員工代號 = B.員工代號
WHERE A.出勤年月 = '10711'
AND A.出勤日 = '14'
AND A.曠職時數 = 4.5
AND B.刷卡年月 = '10711'
AND B.14日下班時間 > 1500

(AXXX <==真正要 update 的 table name )
(BXXX <==真正要 left join 的 table name )
14日下班時間 <= 欄位名怪怪的

AS400 DB2 試試 :

UPDATE AXXX as A
SET A.曠職時數 = 0
WHERE A.出勤年月 = '10711'
AND A.出勤日 = '14'
AND A.曠職時數 = 4.5
AND EXISTS
(
SELECT *
FROM BXXX as B
WHERE B.刷卡年月 = '10711'
AND B.14日下班時間 > 1500
AND A.員工代號=B.員工代號
)

https://oscarvalles.wordpress.com/2013/05/12/db2-update-with-inner-joins/

b7307024 iT邦新手 3 級 ‧ 2018-11-18 04:53:21 檢舉

rogeryao大,這篇連結我有看過,也執行過,但得到悲慘的結果,也就是把所有曠職時數都歸零了,好在可以復原回來。不過看到你回復EXISTS這個用法後,我想到一個新的方法,不過要測試看看才知道可不可行。

rogeryao iT邦超人 8 級 ‧ 2018-11-18 11:59:44 檢舉

方法 1:使用上式(已修改過)
方法 2:改用下式

UPDATE AXXX as A
SET A.曠職時數 = 0
WHERE A.出勤年月 = '10711'
AND A.出勤日 = '14'
AND A.曠職時數 = 4.5
AND A.員工代號 in (
SELECT B.員工代號
FROM BXXX as B
WHERE B.刷卡年月 = '10711'
AND B.14日下班時間 > 1500
)

b7307024 iT邦新手 3 級 ‧ 2018-11-19 15:24:22 檢舉

非常感謝rogeryao大的幫忙!我測試了兩種語法都可以得到我要的結果,有些小小的心得分享如下:

使用EXISTS
不推薦使用此語法,會容易誤解,子查詢若是弄錯的話,就GG了,且AS400執行會跳警告

You have entered a subquery that contains a correlation
without qualification for:                             
  Field. . . . . . . . . . . . .:    ******            

使用IN
WHERE條件值明確,容易理解。

2
做工仔人!
iT邦大師 1 級 ‧ 2018-11-16 17:15:16

UPDATE
((SELECT 員工代號、出勤年月、出勤日、曠職時數
FROM A
LEFT JOIN B ON A.員工代號 = B.員工代號
WHERE (出勤年月 = '10711' AND 出勤日 = '14' AND 曠職時數 = 4.5)
AND (刷卡年月 = '10711' AND 14日下班時間 > 1500 )) B
WHERE A.員工代號 = B.員工代號 AND A.出勤年月 = B.出勤年月 AND A.出勤日 = B.出勤日)
SET A.曠職時數 = 0

這樣試試 !!
有效再解釋原因.(因為DB2我沒用過, 我用Oracle 的方式)

b7307024 iT邦新手 3 級 ‧ 2018-11-16 17:32:43 檢舉

抱歉,請問一下

AND (刷卡年月 = '10711' AND 14日下班時間 > 1500 )) B  

這個B需要改成C嗎?? 謝謝!

b7307024 iT邦新手 3 級 ‧ 2018-11-16 17:40:25 檢舉

我照您的做法,結果還是不行。
Token ( was not valid. Valid tokens: .

0
paicheng0111
iT邦大師 5 級 ‧ 2018-11-16 18:22:40

我先前問過在ACCESS上類似的問題,樓主可以研究看看。

https://ithelp.ithome.com.tw/questions/10189788

b7307024 iT邦新手 3 級 ‧ 2018-11-18 04:46:40 檢舉

pcw大,我修改我的程式碼後,執行結果如下:

Keyword INNER not expected. Valid tokens: SET.

我的案例用INNER JOIN,你的CASE可能要用LEFT JOIN

b7307024 iT邦新手 3 級 ‧ 2018-11-18 12:03:09 檢舉

一樣是不行

Keyword LEFT not expected. Valid tokens: SET.
0
Andy Chiu
iT邦研究生 3 級 ‧ 2018-11-17 22:09:51

請問這個SQL是從AS/400執行嗎?還是MS Access連結AS/400的資料表後執行?
如果是後者,那無法更新資料,只能查詢

b7307024 iT邦新手 3 級 ‧ 2018-11-18 10:23:26 檢舉

是前者,從AS/400執行。

我要發表回答

立即登入回答