INSERT INTO ... VALUES () when not exists

Question

I have insert:

INSERT INTO barrier
(barrier_id,
 usage_id,
 type_id,
 currency_id,
 modified_date,
 version_nr)
VALUES (barrier_seq.nextval,
        (SELECT usage_id
         FROM usages
         WHERE id =
               (SELECT id
                FROM products
                WHERE identifier =
                      (SELECT identifier
                       FROM identifiers
                       WHERE type_id =
                             (SELECT type_id
                              FROM types
                              WHERE NAME = 'New product')
                         AND LOCATION = 'USA')
                  AND status = 'DONE')),
        (SELECT type_id FROM types WHERE name = 'My name'),
        5,
        CURRENT_TIMESTAMP,
        3);

I know that I can use where not exists when there is SELECT in INSERT instead of VALUES() (Oracle insert if not exists statement). Is it somehow possible in this case without changing this INSERT ... VALUES() into INSERT... SELECT?

score 1 · Accepted Answer · answered Mar 17 '21 at 11:19

Use insert . . . select and add the condition to the end of the SELECT:

INSERT INTO barrier (barrier_id, usage_id, type_id, currency_id,  modified_date, version_nr)
    SELECT barrier_seq.nextval, usage_id,
           (SELECT type_id FROM types WHERE name = 'My name'),
           5, CURRENT_TIMESTAMP, 3)
    FROM usages
    WHERE id = (SELECT id
                FROM products
                WHERE identifier =
                      (SELECT identifier
                       FROM identifiers
                       WHERE type_id =
                             (SELECT type_id
                              FROM types
                              WHERE NAME = 'New product'
                             ) AND
                             LOCATION = 'USA'
                      ) AND
                      status = 'DONE'
              ) AND
              NOT EXISTS ( . . . );

INSERT INTO ... VALUES () when not exists

1 Answers1