Nyx Assasin Nyx Assasin - 4 months ago 5
MySQL Question

How to update a column based on condtions

I have a MySQL Table and it looks like this.

+----------+-----------+----------+--------------+-------------+
| 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 |
+----------+-----------+----------+--------------+-------------+


and as time goes by the column
ReflectedQty
will be updated and it might look like this

+----------+-----------+----------+--------------+-------------+
| 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 |
+----------+-----------+----------+--------------+-------------+


My Question is how can i update the Column
OtherStatus
to
Partially Received
or
Fully Received
if Column
ReflectedQty
=
0.00
?

How can i achieve this using select command?

I will explain this by code(sample)

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


any other code accepted

TYSM

Answer

Query would look like below:

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

valueToBeDeducted is an input parameter

And if you want to update all at a time:

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