Answers for "How to update field to subtract value to existing value if that value is lesser than the existing value balance value should be subtract from the next coloumn in MySQL"

SQL
0

How to update field to subtract value to existing value if that value is lesser than the existing value balance value should be subtract from the next coloumn in MySQL

// These are the inputs you will be getting.
$pid = 321;
$qty = 7;

// Fetch all the relevent rows using the below query statement.
$select_sql = "select * from products_stock where pid = {$pid} and amount > 0";

// This will return the below array.
$data = [
    [
        'id' => 1,
        'zid' => 123,
        'pid' => 321,
        'amount' => 1,
    ],
    [
        'id' => 1,
        'zid' => 124,
        'pid' => 321,
        'amount' => 5,
    ],
    [
        'id' => 1,
        'zid' => 125,
        'pid' => 321,
        'amount' => 10,
    ],
];

$update_data = [];

// You can then loop through your rows to perform your logic
foreach  ($data as $row) {
    // Exit loop if qty is 0
    if ($qty == 0) {
        break;
    }

    $id = $row['id'];
    $amount = $row['amount'];

    // Subtract the required amount from qty.
    $qty -= $amount;
    $amount = 0;

    // If extra qty was consumed, add back to the amount.
    if ($qty < 0) {
        $amount =+ ($qty * -1);
        $qty = 0;
    }

    // Update you data here or the best practice would be to avoid sql in a loop and do a bulk update.
    // You can do this based on your requirement.
    // $update_sql = "update products_stock set amount = {$amount} where id = {$id}";

    // Prepare date for bulk update
    $update_data []= [
        'id' => $id,
        'amount' => $amount,
    ];

    echo "Qty {$qty} | Amt {$amount} \n";
    echo "--------\n";
}

// Bulk update all your rows
if (count($update_data) > 0) {
    $this->db->update_batch('products_stock', $update_data, 'id'); 
}

echo "\n";
echo "Balance Qty ". $qty . "\n";
Posted by: Guest on January-08-2021

Code answers related to "How to update field to subtract value to existing value if that value is lesser than the existing value balance value should be subtract from the next coloumn in MySQL"

Code answers related to "SQL"

Browse Popular Code Answers by Language