Insert/Update in PL/SQL
10,443
Solution 1
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
Solution 2
I can not really see a point in doing a cursor in this case. Why can't you just do it like this:
--Update the rows
UPDATE MAIN
SET ID=regions.REGION_ID,
CODE_NUMBER=regions.[description]
FROM MAIN
JOIN regions
ON MAIN.ID=regions.REGION_ID;
--Insert the new ones
INSERT INTO MAIN(ID,CODE_NUMBER)
SELECT
regions.REGION_ID,
regions.[description]
FROM
regions
WHERE NOT EXISTS
(
SELECT
NULL
FROM
MAIN.ID=regions.REGION_ID
)
Author by
Femme Fatale
Updated on June 14, 2022Comments
-
Femme Fatale almost 2 years
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;