8 Replies Latest reply on Mar 26, 2009 4:36 AM by 617417

    Multiple columns and rows update using select staement

    617417
      i am trying to update the 2 colums of a table based on the cursor result set with key values are matching.



      DECLARE

      CURSOR update_master
      IS
      SELECT B.cust_num,c.cust_num,
      B.cust_name,B.cust_bal
      FROM
      Table_one B
      JOIN
      Table_two C
      ON B.cust_num= C.cust_num;

      BEGIN

      FOR master_rec IN update_master

      LOOP

      UPDATE Table_three
      SET col1 = master_rec.cust_name,
      Col2 = master_rec.cust_bal,
      Where WHERE Cust_num = master.cust_num;

      END LOOP;
      COMMIT;
      END;

      The 3 tables are having around 50000 records individually............

      It is taking too much of time to execute......more than 6 minutes......

      how can we optimize it?? can we write this update statement directly? instead of writing it in procedure?