This discussion is archived
3 Replies Latest reply: Nov 26, 2012 10:20 AM by 885842 RSS

Best way to update a table with disinct values

885842 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    >
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Fantastic - have never used merge before, this is exactly was i was looking for.

    Thank you !!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points