UPDATE o
SET o.total_payment_received = tpr.total
FROM Orders o INNER JOIN 
(SELECT Pay_OrderID, SUM ( CASE 
WHEN pay_result IN ('DEBIT','CAPTURE') THEN pay_amount 
WHEN pay_result = 'CREDIT' THEN -1*pay_amount 
ELSE 0 
END) AS total
FROM Payment_Log
GROUP BY Pay_OrderID) AS tpr
ON o.OrderID = tpr.Pay_OrderID
WHERE o.PaymentAmount <> o.Total_Payment_Received AND o.OrderStatus <> 'Cancelled' AND o.OrderDate < DATE_SUB(NOW(), INTERVAL 30 MINUTE)