Oracle PLSQL Insert only if doesn't exist. [duplicate]

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 stackoverflow.com/questions/10824764/…, or stackoverflow.com/questions/8185250/sql-insert-if-not-exists or stackoverflow.com/questions/3841441/insert-if-row-dont-exist ... and more — Ben
– Ben, Commented Oct 2, 2012 at 16:42

hkutluay · Accepted Answer · 2012-10-02 16:42:01Z

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 );

Collectives™ on Stack Overflow

Oracle PLSQL Insert only if doesn't exist. [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related