Insert/Update in PL/SQL

oracle11g, plsql, plsqldeveloper, sql

Solution

There's no need to do this with PL/SQL and cursors. What you really want to do is something like this:

MERGE INTO MAIN dst
USING (
  SELECT regions.REGION_ID id,
         regions.REGION_NAME description
  FROM   regions
) src
ON src.id = dst.id
WHEN MATCHED THEN UPDATE 
  SET dst.code_number = src.description
WHEN NOT MATCHED THEN INSERT (id, code_number)
  VALUES (src.id, src.description)

Read more about the `SQL MERGE` statement in the documentation

Problem

I have made a procedure in PL/SQL which inserts data from one table to another on basis of primary key. My procedure is working fine but i can't figure out how will i update column CODE_NUMBER of my table MAIN if primary key already exists. Actually i want rows of MAIN table to get UPDATED when its has primary key and insert data from REGIONS when primary key does not exists. ``` DECLARE variable number; id number; description varchar2 (100); CURSOR C1 IS select regions.REGION_ID variable from regions; BEGIN FOR R_C1 IN C1 LOOP BEGIN select regions.REGION_ID,regions.REGION_NAME into id,description from regions where regions.REGION_ID = R_C1.variable; ----If exists then update otherwise insert INSERT INTO MAIN( ID, CODE_NUMBER) VALUES( id,description); dbms_output.put_line( id ||' '|| 'Already Exists'); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN dbms_output.put_line( R_C1.variable); END; END LOOP; END; ```

Original source