3 Replies Latest reply: Nov 26, 2012 12:20 PM by 885842 RSS

    Best way to update a table with disinct values

    885842
      Hi, i would really appreciate some advise:

      I need to reguarly perform a task where i update 1 table with all the new data that has been entered from another table. I cant perform a complete insert as this will create duplicate data every time it runs so the only way i can think of is using cursors as per the script below:


      CREATE OR REPLACE PROCEDURE update_new_mem IS
      tmpVar NUMBER;

      CURSOR c_mem IS
      SELECT member_name,member_id
      FROM gym.members;
      crec c_mem%ROWTYPE;


      BEGIN
      OPEN c_mem;
      LOOP
      FETCH c_mem INTO crec;
      EXIT WHEN c_mem%NOTFOUND;
      BEGIN
      UPDATE gym.lifts
      SET name = crec.member_name
      WHERE member_id = crec.member_id;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN NULL;
      END;
      IF SQL%NOTFOUND THEN
      BEGIN
      INSERT INTO gym.lifts
      (name,member_id)
      VALUES (crec.member_name,crec.member_id);
      END;
      END IF;
      END LOOP;
      CLOSE c_mem;

      END update_new_mem;



      This method works but is there an easier (faster) way to update another table with new data only?

      Many thanks
        • 1. Re: Best way to update a table with disinct values
          rp0428
          >
          This method works but is there an easier (faster) way to update another table with new data only?
          >
          Almost anything would be better than that slow-by-slow loop processing.

          You don't need a procedure you should just use MERGE for that. See the examples in the MERGE section of the SQL Language doc
          http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm
          MERGE INTO bonuses D
             USING (SELECT employee_id, salary, department_id FROM employees
             WHERE department_id = 80) S
             ON (D.employee_id = S.employee_id)
             WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
               DELETE WHERE (S.salary > 8000)
             WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
               VALUES (S.employee_id, S.salary*.01)
               WHERE (S.salary <= 8000);
          • 2. Re: Best way to update a table with disinct values
            ranit B
            is there an easier (faster) way to update another table with new data only?
            Your requirement exactly meets the purpose of the MERGE statement.

            When there is a need, where you need to process a set of data and based on some condition it needs to be INSERTED or UPDATED.
            So, instead of performing this in a LOOP i.e. row-by-row (slow-by-slow) manner, you can do this using a single SQL statement.

            And always SQL is faster than Pl/Sql (as there's no context switching between SQL Engine and Pl/Sql Engine).

            HTH
            Ranit B.
            • 3. Re: Best way to update a table with disinct values
              885842
              Fantastic - have never used merge before, this is exactly was i was looking for.

              Thank you !!