Update status after insert query

Question

$stmt = "insert into {CI}payment_logs (customer_id, invoice_id, invoice_total_amount, invoice_paid_amount, customer_paid_amount, invoice_payment_on ) "
                . "values( ?, ?, ?, ?, ?, NOW() )";
$this->db->query( $stmt, array( $Customer_Id, $Invoice_Id, $Invoice_Total_Amount, $Deducted_Amount, $Paid_By_Cust ) );        
$Log_Id = $this->db->insert_id(); // Returns Last Insert ID

This is the which is working fine according to my Need, But i have to update the status of every single row in the same table in a specific condition. Take a look on the picture,

This is the Inserted Data by the above query. As you see in the invoice_total_amount is the Total Amount, But this amount complete in the third row data. I have to update the invoice_status field as paid after the height-lighted amounts SUM is equal to the invoice_total_amount and in the rest field i have to update the status as partial. I tried to update the status to adding a trigger but not succeed.

Then i tried with PHP with this command.

update {CI}payment_logs 
    set invoice_status = IF( invoice_total_amount = sum(invoices_paid_amount), 'paid', 'partial' ) 
where log_id = ?

But i didn't get the SUM of invoices_paid_amount where invoice_id = 5, i tried with subquery in IF condition but it is not valid my query.

Please help me to resolve it with Single query or Trigger solution..

Answer 1

Join with a subquery that calculates the sum.

UPDATE {CI}payment_logs l1
JOIN (SELECT invoice_id, SUM(invoices_paid_amount) total
      FROM {CI}payment_logs
      GROUP BY invoice_id) l2
ON l1.invoice_id = l2.invoice_id
SET invoice_status = IF(invoice_total_amount = total, 'paid', 'partial')
WHERE log_id = ?