mysql - 如何根据条件更新列

Question

我有一个 MySQL 表，它看起来像这样。

+----------+-----------+----------+--------------+-------------+
| PONO     | ItemCode  | OrderQty | ReflectedQty | OtherStatus |
+----------+-----------+----------+--------------+-------------+
| PO787HZN | HKQSLUWKN | 30.00    | 30.00        | Posted      |
| PO787HZN | SORHFRBPJ | 40.00    | 40.00        | Posted      |
| PO787HZN | OP8XMREC0 | 50.00    | 50.00        | Posted      |
| PO787HZN | CPD5CGDZ3 | 60.00    | 60.00        | Posted      |
+----------+-----------+----------+--------------+-------------+

随着时间的推移，ReflectedQty 列将被更新，它可能看起来像这样

    +----------+-----------+----------+--------------+-------------+
    | PONO     | ItemCode  | OrderQty | ReflectedQty | OtherStatus |
    +----------+-----------+----------+--------------+-------------+
    | PO787HZN | HKQSLUWKN | 30.00    | 20.00        | Posted      |
    | PO787HZN | SORHFRBPJ | 40.00    | 1.00         | Posted      |
    | PO787HZN | OP8XMREC0 | 50.00    | 5.00         | Posted      |
    | PO787HZN | CPD5CGDZ3 | 60.00    | 6.00         | Posted      |
    +----------+-----------+----------+--------------+-------------+

我的问题是，如果列 ReflectedQty，我如何将列 OtherStatus 更新为 Partially Received 或 Fully Received = 0.00?

我如何使用 select 命令实现这一点？

我将通过代码(示例)来解释这一点

sqlcommand = select reflectedqty from table where reflectedqty = 0.00 and PONo = PO787HZN

if all ReflectedQty of PO787HZN = 0.00 then
'Update OtherStaus to = Fully Received
else
'Update OtherStaus to = Partially Received
end if

接受任何其他代码

泰斯姆

Answer 1

查询如下所示:

UPDATE your_table A
SET A.ReflectedQty = A.ReflectedQty - @valueToBeDeducted,
 A.OtherStatus =
                IF (
                        (A.ReflectedQty - @valueToBeDeducted) <= 0,
                        'Fully Receieved',
                        'Partially Receieved'
                )
WHERE...

_{valueToBeDeducted 是一个输入参数}

如果您想一次全部更新:

UPDATE your_table A
SET A.OtherStatus =
IF (
    A.ReflectedQty <= 0,
    'Fully Receieved',
    'Partially Receieved'
)
WHERE <your_condtion_here>