伊莉討論區

標題: MySQL 的 Update問題求解 [打印本頁]

作者: u48326 時間: 2020-7-8 06:04 PM 標題: MySQL 的 Update問題求解

需求：

先匯整出 N表，然後根據 N表、在 P表中查找到對應的 id 及 postdate，去更新 P表的２個欄位：post_qty 和remaining_qty。

先更新 post_qty ，再根據異動後的 post_qty來重新計算 remaining_qty。

remaining_qty = N.ad_qty – P.post_qty

執行後發現，remaining_qty 不會用新的post_qty來計算；它一直是用 post_qty異動前的舊值來計算。

請高人指點：這是 MySQL 的特性嗎？還是哪裡的語句要調整？

SQL 語句如下：

update custom_data_1020 P,

( SELECT DATA_1 receipt_no, DATA_2AS application_date, DATA_23 AS postdate_begin, DATA_25 AS postdate_end,

DATA_10_8 ad_id, B.ad_qty, L.postdate, L.id, count(*)qty

FROM zzzz_flow_data_12 M

JOINzzzz_flow_data_12_10 D ON D.run_id = M.run_id

JOINcustom_data_1020 L ON L.id = D.DATA_10_8 AND L.postdate BETWEEN M.DATA_23 ANDM.DATA_25

JOINcustom_data_1010 B ON B.id = D.DATA_10_8

WHERE M.DATA_2 =STR_TO_DATE('2020-07-08', "%Y-%m-%d" )

GROUP BY id, postdate

ORDER BY id, postdate

) as N

set post_qty = post_qty + N.qty,remaining_qty = N.ad_qty - post_qty

where P.id = N.id and P.postdate = N.postdate

[attach]132455244[/attach]

作者: Sharr 時間: 2020-7-21 09:15 PM

這應該是MySQL 特性..
MySQL 8.0 說明
拉到中間有一段解釋.. 如果要按照順序如你想的. 變成要單純的針對同一個table做動作才能保證會按照順序從左到右依據新的值來更新, 但你有用到join 條件, 所以這部分MySQL無法保證順序.

The second assignment in the following statement sets col2 to the current (updated) col1 value, not the original col1 value. The result is that col1 and col2 have the same value. This behavior differs from standard SQL.

UPDATE t1 SET col1 = col1 + 1, col2 = col1;

Single-table UPDATE assignments are generally evaluated from left to right.
For multiple-table updates, there is no guarantee that assignments are carried out in any particular order.

作者: danz0403 時間: 2020-7-24 10:21 AM

如果目的是要讓 remain_qty用 post_qty的新值更新, 而結果卻是固定使用post_qty的舊值的話,
那麼解決方案只要把post_qty的更新式, 也放一份到remain_qty的更新式子就行了。

所以就是把第12行改為:
set post_qty = post_qty + N.qty,remaining_qty = N.ad_qty - post_qty -N.qty

試試看吧!

歡迎光臨伊莉討論區 (http://4.eyny.com/)