我有两个表，如下所示:

左表根据 SRNumber 列填充了数据，之后您可以看到表 stockrequisition 和表 generalinventory cóItemCode Và Qty 列中的相同数据(左表中的 RequestedQty)

mySQL 命令

UPDATE GeneralInventory AS tb1 
INNER JOIN StockRequisition AS tb2  
ON (tb1.ItemCode= tb2.ItemCode) 
SET tb1.Qty = Format(tb1.Qty - tb2.RequestedQty,2) 
WHERE tb2.SRNumber = 'SR487SHL'

并且该命令位于代码工作的按钮 Update 处

代码输出

GeneralInventory.Qty = GeneralInventory.Qty - StockRequisition.RequestedQty TRONG StockRequisition.SRNumber = SR487SHL Và GeneralInventory.ItemCode = StockRequisition.ItemCode

câu hỏi

如何确定 GeneralInventory.Qty 的输出是否变为负数，以便我可以停止更新命令。像这样

    if the future output of GeneralInventory.Qty < 0 or Negative then
        Msg("Unable to update your request because some of Items Qty will result to Negative,Please review it first")
khác
'Do the Update Command
End If

这是我在 VB.Net 中的全部代码

 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    one()
    two()
End Sub
Private Sub one()
    Dim con1 As MySqlConnection = New MySqlConnection("server=localhost;userid=root;password=admin1950;database=inventory")
    Dim sql1 As MySqlCommand = New MySqlCommand("select SRNumber,ItemCode,RequestedQty from stockrequisition where SRNumber = 'SR487SHL'", con1)
    Dim ds1 As DataSet = New DataSet
    Dim adapter1 As MySqlDataAdapter = New MySqlDataAdapter
    con1.Open()
    adapter1.SelectCommand = sql1
    adapter1.Fill(ds1, "MyTable")
    DataGridView1.DataSource = ds1.Tables(0)
    con1.Close()
End Sub
Private Sub two()
    Dim con1 As MySqlConnection = New MySqlConnection("server=localhost;userid=root;password=admin1950;database=inventory")
    Dim sql1 As MySqlCommand = New MySqlCommand("select ItemCode,Qty from generalinventory", con1)
    Dim ds1 As DataSet = New DataSet
    Dim adapter1 As MySqlDataAdapter = New MySqlDataAdapter
    con1.Open()
    adapter1.SelectCommand = sql1
    adapter1.Fill(ds1, "MyTable")
    DataGridView2.DataSource = ds1.Tables(0)
    con1.Close()
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim con1 As MySqlConnection = New MySqlConnection("datasource=localhost;database=inventory;userid=root;password=admin1950")
    Dim cmdinsert As MySqlCommand = New MySqlCommand("UPDATE GeneralInventory AS tb1 INNER JOIN StockRequisition AS tb2 ON (tb1.ItemCode= tb2.ItemCode) SET tb1.Qty = Format(tb1.Qty - tb2.RequestedQty,2)WHERE tb2.SRNumber = 'SR487SHL' AND tb1.Qty >= tb2.RequestedQty", con1)
    con1.Open()
    cmdinsert.ExecuteNonQuery()
    con1.Close()
    one()
    two()
End Sub

我希望你能得到我，TYSM future 的帮助。

1 Câu trả lời

只需使用CASE WHEN:

UPDATE GeneralInventory AS tb1 
INNER JOIN StockRequisition AS tb2  
ON (tb1.ItemCode= tb2.ItemCode) 
SET tb1.Qty = CASE WHEN tb1.Qty - tb2.RequestedQty < 0 THEN tb1.Qty THEN Format(tb1.Qty - tb2.RequestedQty,2) END
WHERE tb2.SRNumber = 'SR487SHL'

Hoặc bạn có thể sử dụngIF:

UPDATE GeneralInventory AS tb1 
INNER JOIN StockRequisition AS tb2  
ON (tb1.ItemCode= tb2.ItemCode) 
SET tb1.Qty = IF(tb1.Qty - tb2.RequestedQty < 0, tb1.Qty, Format(tb1.Qty - tb2.RequestedQty,2))
WHERE tb2.SRNumber = 'SR487SHL'

或者你可以这样做:

UPDATE GeneralInventory AS tb1 
INNER JOIN StockRequisition AS tb2  
ON (tb1.ItemCode= tb2.ItemCode) 
SET tb1.Qty = Format(tb1.Qty - tb2.RequestedQty,2)
WHERE tb2.SRNumber = 'SR487SHL'
AND tb1.Qty >= tb2.RequestedQty

如果你想返回消息，那么你必须创建一个过程。

关于mysql - 如果可能的输出为零，则不要更新，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/38365392/