DECLARE CURSOR cur IS SELECT c.account_id FROM crm_statement_fulfilled_sid c; BEGIN FOR i IN cur LOOP UPDATE crm_statement_fulfilled_sid a SET a.rewards_expired = (SELECT abs(nvl(SUM(t.VALUE), 0)) FROM crm_reward_txns t WHERE txn_date BETWEEN (SELECT (decode(MAX(csf.procg_date) + 1, NULL, to_date('01-May-2011', 'DD-MM-YYYY'), MAX(csf.procg_date) + 1)) FROM crm_statement_fulfilled csf WHERE csf.procg_date < '01-aug-2012' AND account_id = i.account_id) /*points_start_date_in*/ AND '01-aug-2012' AND reason = 'RE' AND t.account_id = i.account_id); END LOOP; END;
Hoek wrote:Well to be nitpicking. The op already does a single big update. He just does it again and again and again for each account id. Each time completely overwriting the values from the last account id.
Second: you're doing row-by-row (= slow-by-slow) processing.
See if you can rewrite your code into a single SQL UPDATE statement that will process an entire SET at once.