Oracle PLSQL Insert only if doesn't exist.

Question

Possible Duplicate:
oracle insert if row not exists

I am using the query below to calculate a total for some data, however, I want the insert to only be executed if the ID doesn't exist in the EL_CLIENT_REC_UNITS in the first place. Can anyone see a way of doing this?

     INSERT INTO EL_CLIENT_REC_UNITS 
     (ID,
     EL_CLIENT_ID,
     RECORDING_ID,
     UNIT_TYPE,
     PHY_DIG_OR_TOTAL,
     CONTROLLED_PERCENTAGE,
     PERC_ADJ_UNITS_RECEIVED,
     SUM_UNITS_RECEIVED) (       
      (SELECT 
        EL_CLIENT_ID||RECORDING_ID||'CT'   AS ID,
        EL_CLIENT_ID                 AS EL_CLIENT_ID, 
        RECORDING_ID                 AS RECORDING_ID, 
        UNIT_TYPE                    AS UNIT_TYPE, 
        'Total'                      AS PHY_DIG_OR_TOTAL, 
        NULL AS CONTROLLED_PERCENTAGE,
        SUM(PERC_ADJ_UNITS_RECEIVED) AS PERC_ADJ_UNITS_RECEIVED,
        SUM(SUM_UNITS_RECEIVED)      AS SUM_UNITS_RECEIVED
    FROM EL_CLIENT_REC_UNITS
    WHERE EL_CLIENT_ID = pRow.ID
    AND UNIT_TYPE = 'Cleared'
    GROUP BY UNIT_TYPE, EL_CLIENT_ID, RECORDING_ID)
    WHERE NOT EXISTS (SELECT );

or http://stackoverflow.com/questions/10824764/oracle-insert-if-not-exists-statement, or http://stackoverflow.com/questions/8185250/sql-insert-if-not-exists or http://stackoverflow.com/questions/3841441/insert-if-row-dont-exist ... and more — Ben, Oct 02 '12 at 16:42

score 1 · Accepted Answer · answered Oct 02 '12 at 16:42

Try this

INSERT INTO EL_CLIENT_REC_UNITS 
     (ID,
     EL_CLIENT_ID,
     RECORDING_ID,
     UNIT_TYPE,
     PHY_DIG_OR_TOTAL,
     CONTROLLED_PERCENTAGE,
     PERC_ADJ_UNITS_RECEIVED,
     SUM_UNITS_RECEIVED)       
    SELECT * FROM
    (SELECT 
        EL_CLIENT_ID||RECORDING_ID||'CT'   AS ID,
        EL_CLIENT_ID                 AS EL_CLIENT_ID, 
        RECORDING_ID                 AS RECORDING_ID, 
        UNIT_TYPE                    AS UNIT_TYPE, 
        'Total'                      AS PHY_DIG_OR_TOTAL, 
        NULL AS CONTROLLED_PERCENTAGE,
        SUM(PERC_ADJ_UNITS_RECEIVED) AS PERC_ADJ_UNITS_RECEIVED,
        SUM(SUM_UNITS_RECEIVED)      AS SUM_UNITS_RECEIVED
    FROM EL_CLIENT_REC_UNITS
    WHERE EL_CLIENT_ID = pRow.ID
    AND UNIT_TYPE = 'Cleared'
    GROUP BY UNIT_TYPE, EL_CLIENT_ID, RECORDING_ID) INS_VALS
    WHERE NOT EXISTS (SELECT 1 FROM EL_CLIENT_REC_UNITS ELC WHERE ELC.ID =  INS_VALS.ID );

Oracle PLSQL Insert only if doesn't exist.

1 Answers1