CODEWITHSUNDEEP
oracle-databaseplsqlcursor
VeryNew
VeryNew

Reputation: 5

PL/SQL ONLY using a cursor to retrieve data from 2 tables into a new table

Hi I need to create a new table to store the data of two tables. Then I would need to create a stored procedure to transfer the data from the two tables into the new one. I had no problem transferring the data from the first table to the new table, the problem arises when i transfer the data from the second table to the new table. Because instead of being inserted in the row that matches its pubid, it is being inserted into a new row instead.

Upvotes: 0

Views: 337

Answers (1)

Littlefoot
Littlefoot

Reputation: 142743

You're quite close, I'd say.

In the second loop, you shouldn't INSERT, but UPDATE existing row.

Cursor you declared:

CURSOR cur_proceedings IS
SELECT pubid, year FROM proceedings;

You've used it here:

OPEN cur_proceedings;
LOOP
    FETCH cur_proceedings INTO v_proceedings;
    EXIT WHEN cur_proceedings%NOTFOUND;

    -- Instead of INSERT:
    -- INSERT INTO publication_master(proceeding_year)
    -- VALUES(v_proceedings.year);

    -- use UPDATE:
    update publication_master m set
      m.proceeding_year = v_proceedings.year
      where m.pubid = v_proceedings.pubid;
    
END LOOP;

CLOSE cur_proceedings;

Upvotes: 1

Related Questions