Update next line if current updated line is 0
My table looks like this:
HarvestID | CheckIn | Actual | Status
1 | Date | 100 | Process
2 | Date | 200 | Process
3 | Date | 400 | Process
and my problem is how to update the following line if I set the value Qty = '350' and then update the id '1' and '2' to 0 and ID '3' Qty = '350'
Is there a way to do this without forcing the new table to stock the sum of "A" and using a stored procedure?
I am already trying:
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
0
source to share
1 answer
You can do it 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
source to share