Update or Insert based on the value of a column in the table

Question

Possible Duplicate:
Oracle: how to UPSERT (update or insert into a table?)

Could you guys give me an suggestion on how to proceed in the below situation:

Read table 2 column 1 
if value says the record exists in table 1
update table 1 record with table 2 record details
else(value says the record does not exist in table 1)
insert table 1 record with table 2 record details

I am beginner to Oracle SQL, Please let me know if there is a better approach..I was thinking about using cursors to solve this..

Can you explain a bit more? Why can't you check against table 1? — Matthew, Jul 02 '12 at 17:42

score 6 · Answer 1 · answered Jul 02 '12 at 18:09

6

The simplest answer is to use the merge statement:

MERGE INTO table1 a
USING ( select column1, column2 
          from table2 ) b
ON ( a.column1 = b.column1 )
WHEN MATCHED THEN 
  update set a.column2 = b.column2
WHEN NOT MATCHED THEN 
  insert (a.column1, a.column2)
  values (b.column1, b.column2)

Simply put this takes everything from the select on table2. It then joins this query to table1 on the condition. If there is a "match" then it updates, otherwise inserts.

The documentation has more information about various additional options that you don't, currently, require.

answered Jul 02 '12 at 18:09

Ben

51,770
36
127
149

Thanks Ben, it almost served the purpose..first time it ran successfully but if i am running again it is throwing the error `unable to get a stable set of rows in the source tables` any work around for this..tried with Distinct as mentioned in one of the other threads but it did not work. – javanoob Jul 03 '12 at 04:03
Beware - there were numerous bugs concerning this feature in oracle9. We were even forbidden to use it until upgrade to oracle10. – Iľja Jul 03 '12 at 06:15
@javanoob, it sounds like you don't have a unique set of rows after the first time you run it, i.e. you don't have a unique constraint / index on the table. Are the columns you're joining on unique _after_ you've run it once? – Ben Jul 03 '12 at 07:40

score 1 · Answer 2 · answered Jul 02 '12 at 17:52

1

Have a look at the merge statement.

http://psoug.org/reference/merge.html

answered Jul 02 '12 at 17:52

crowne

8,456
3
35
50

Update or Insert based on the value of a column in the table

2 Answers2