0

I have a table with two number columns, and a unique constraint over them both. I would like to insert a new pair of values UNLESS the pair already exists. What is the simplest way to do this?

If I do 

insert into TABLE values (100,200)

and the pair already exists I get a ORA-00001 error, so I would like to do something like 

insert or update into TABLE values (100,200)
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Svein Bringsli
  • 5,640
  • 7
  • 41
  • 73
  • 2
    if the pair of values already exists, what are you trying to update? – Nellius Oct 06 '10 at 13:49
  • 4
    possible duplicate of [Oracle: how to UPSERT (update or insert into a table?)](http://stackoverflow.com/questions/237327/oracle-how-to-upsert-update-or-insert-into-a-table) – Tony Andrews Oct 06 '10 at 13:56
  • @Nellius: Nothing, really. But if they _don't_ exist I would like to insert them. The values come from an external source. I would like to insert them into the database, but I don't want to write extra code that first checks whether they are already present. – Svein Bringsli Oct 06 '10 at 13:57
  • @Tony Andrews: Yes, and in addition your answer there (merging the "old fashioned way") worked very well for me. Thanks. Should I close this as duplicate, or would you like to re-enter your answer for the reputation? :-) – Svein Bringsli Oct 06 '10 at 14:04
  • 1
    I don't think that would be cricket! – Tony Andrews Oct 06 '10 at 14:26

2 Answers2

7

You can use MERGE

Michael Pakhantsov
  • 24,855
  • 6
  • 60
  • 59
1

You can try something like:

insert into table
select :a, :b from dual
where not exists (select 1 from table where column1 = :a and column2=:b)
Paulo Guedes
  • 7,189
  • 5
  • 40
  • 60