本文介绍了如果当前更新的第 0 行,则更新下一行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一张看起来像这样的桌子:
I have a table looks like this :
HarvestID | CheckIn | Actual | Status 1 | Date | 100 | Process 2 | Date | 200 | Process 3 | Date | 400 | Process我的问题是如果我设置了 Qty='350' 然后更新 ID '1' & 如何更新下一行'2' 到 0,ID '3' Qty='350'
and my problem is how to update next row if I set the Qty='350' Then Update ID '1' & '2' to 0, and ID '3' Qty='350'
有没有办法在不创建新表来存储A"数量的总数并使用存储过程的情况下做到这一点?
is there a way to do this without make a new table to stock the Total of 'A' Qty's and using stored procedure ?
我已经尝试过的是:
cmdTemp = New MySqlCommand("SELECT * FROM tb_harvest ORDER BY CheckIn ASC LIMIT 1", cn) sqlrd = cmdTemp.ExecuteReader While sqlrd.Read harvestid = sqlrd("HarvestID") qtyTemp1 = sqlrd("Actual") - Qty : qtyTemp2 = sqlrd("Actual") End While sqlrd.Close() If qtyTemp1 < 0 Then cmdTemp = New MySqlCommand("UPDATE tb_harvest SET Actual=0 WHERE HarvestID=" & harvestid & "; " & _ "UPDATE tb_harvest SET Actual=Actual+" & qtyTemp1 & " WHERE HarvestID=" & harvestid + 1, cn) cmdTemp.ExecuteNonQuery() Else cmdTemp = New MySqlCommand("UPDATE tb_harvest SET Actual=Actual-" & qtyTemp2 & " WHERE HarvestID=" & harvestid, cn) cmdTemp.ExecuteNonQuery() End If 推荐答案你可以用变量来做到这一点:
You can do this with variables:
set @qty = 350; update tb_harvest set actual = (case when @qty <= 0 then 0 when @qty >= actual then if(@qty := @qty - actual, 0, 0) when (@tmp := actual - @qty), then if(@qty := 0, @tmp, @tmp) else actual -- should never happen end) order by harvestid;更多推荐
如果当前更新的第 0 行,则更新下一行
发布评论