UPDATE `invoices` AS `i`
LEFT JOIN `invoices` AS `s`
  ON `s`.`original_invoice_id` = `i`.`id`
 AND `s`.`document_type` = 'cancellation'
SET
  `i`.`status` = CASE
    WHEN COALESCE((SELECT SUM(`p`.`amount`) FROM `invoice_payments` AS `p` WHERE `p`.`invoice_id` = `i`.`id`), 0) + 0.005 >= `i`.`gross_total` THEN 'paid'
    WHEN COALESCE((SELECT SUM(`p`.`amount`) FROM `invoice_payments` AS `p` WHERE `p`.`invoice_id` = `i`.`id`), 0) > 0 THEN 'partially_paid'
    ELSE 'issued'
  END,
  `i`.`paid_at` = CASE
    WHEN COALESCE((SELECT SUM(`p`.`amount`) FROM `invoice_payments` AS `p` WHERE `p`.`invoice_id` = `i`.`id`), 0) + 0.005 >= `i`.`gross_total` THEN COALESCE(`i`.`paid_at`, NOW())
    ELSE NULL
  END
WHERE `i`.`document_type` = 'invoice'
  AND `i`.`status` = 'cancelled'
  AND `s`.`id` IS NULL;