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
  • 314,254
  • 77
  • 507
  • 490
Svein Bringsli
  • 5,570
  • 6
  • 38
  • 72
  • 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,077
  • 5
  • 58
  • 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,038
  • 5
  • 39
  • 60