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
?
Advertisement
Answer
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 ( . . . );