0

I would like to execute an INSERT procedure condition to subject tuple not present within database.

I am not certain how to formulate the following

IF !(SELECT a1, a2, a3, FROM table) THEN
    INSERT VALUES INTO TABLE (a1, a2, a3)
ELSE
    -- Do Nothing
END IF

Would I need to utilize a cursor, grab the tuple, and then check each value against the attributes of the procedure? I thought of that but would prefer not to if unnecessary.

Mushy
  • 2,535
  • 10
  • 33
  • 54
  • 1
    You could use a MERGE statement: http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9016.htm#SQLRF01606 – Tony Andrews May 22 '14 at 15:55
  • Would [this answer](http://stackoverflow.com/questions/11570527/oracle-merge-how-can-i-use-it) pose as a suitable example use of MERGE for what I need? – Mushy May 22 '14 at 16:00
  • Yes, except that you would need a "no-op" for the WHEN MATCHED case. I thought "when matched then null" was allowed but apparently not, it would have to be something like "when matched then t.a1 = t.a1". I think I have realised what you really need and will post an answer. – Tony Andrews May 22 '14 at 16:09
  • 1
    @TonyAndrews - just leave the `WHEN MATCHED` clause out if you don't want to use it - it's optional. – Bob Jarvis - Слава Україні May 22 '14 at 17:22
  • @Tony Provide an answer and I will nominate it. – Mushy May 22 '14 at 17:43
  • @BobJarvis, thanks - I'm afraid I don't use MERGE as often as I should and am never 100% sure of the syntax! – Tony Andrews May 23 '14 at 09:01

1 Answers1

0

You can do something like this:

insert into table (col1, col2, col3)
select 'a1', 'a2', 'a3' from dual
where not exists
   (select null from table where col1='a1' and col2='a2' and col3='a3');
A Nice Guy
  • 2,676
  • 4
  • 30
  • 54
Tony Andrews
  • 129,880
  • 21
  • 220
  • 259