Update status after insert query
mysql, php
Solution
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 = ?
Problem
``` $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..