sql - insert if not exists

Question

I am having trouble with a sql query. I need to insert a row if the same row does not exist already. This is what I have so far:

DECLARE
BEGIN
   FOR FOLDER_ROW IN (SELECT FOLDERID, USERID FROM DATA1.FOLDERS)
      LOOP                       
          IF NOT EXISTS (SELECT * FROM DATA1.FOLDER_USER WHERE FOLDER_ID = FOLDER_ROW.FOLDERID AND USER_ID = FOLDER_ROW.USERID) 
          INSERT INTO DATA1.FOLDER_USER (FOLDER_ID, USER_ID) VALUES (FOLDER_ROW.FOLDERID, FOLDER_ROW.USERID);
     END LOOP;
    COMMIT;
END;

I would not be very familiar with sql particularly the not exists syntax so when I execute I get the following error:

ORA-06550: line 37, column 11: PLS-00103: Encountered the symbol "INSERT" when expecting one of the following:

then and or

The symbol "then" was substituted for "INSERT" to continue.

ORA-06550: line 38, column 10:
PLS-00103: Encountered the symbol "LOOP" when expecting one of the following:

   if
ORA-06550: line 40, column 5:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   end not pragma final instantiable order overriding static
   member constructor map

Answer 1

Do it all in SQL rather than context switching into PL/SQL:

INSERT INTO DATA1.FOLDERS
(folder_id,
 user_id)
SELECT f1.folder_id,
       f1.user_id
  FROM DATA1.FOLDERS f1
 WHERE NOT EXISTS (SELECT 1
                     FROM DATA1.FOLDERS f2
                    WHERE f1.folder_id = f2.folder_id
                      AND f1.user_id = f2.user_id);

Answer 2

A better (Oracle specific?) solution may be the MERGE statement.

See here for a nice explanation, with examples:

http://www.oracle-base.com/articles/10g/MergeEnhancements10g.php

Hope that helps.

Answer 3

You forgot the THEN

IF condition THEN  
   ...
ELSE
    ...
END IF;