CODEWITHSUNDEEP
sqloracle-database
user525146
user525146

Reputation: 3998

upsert into oracle

I have a table table with columns id, name, position, capture_date, modified_date, comments. I am trying to do a simple upsert which is driving me crazy. When the table is empty, it has to insert, but when its not empty it has to update the comments column row which has the same position, if its different it has to insert a new row instead of updating the existing one. When the table is empty, i used this merge statement to create the first row.

This works fine. But, second row has to be

1, john, 2, 01-JUL-15, 23-JUL-15, 'world'

In this case, the data is almost same except that the position value is 2, so a new row has to be inserted instead of updating the existing row's position to 2. That is what my merge statement is doing. Any ideas to work on this please.

    merge into customers a
using(select 1 as customer_id, 'john' as customer_name, '1' as position, '01-JUL-15' as capture_date, 
      sysdate as  modified_date, 'hello' as comments from dual) b
  on(a.customer_id=b.customer_id)
when matched then
update set a.customer_id = b.customer_id, a.customer_name = b.customer_name, 
a.position = b.position, a.capture_date= b.capture_date, a.modified_date = b.modified_date,
a.comments=b.comments
when not matched then
insert(a.customer_id, a.customer_name, a.position, a.capture_date, a.modified_date, a.comments)
values(b.customer_id, b.customer_name, b.position, b.capture_date, b.modified_date, b.comments)

I have created the sqlfiddle

Upvotes: 2

Views: 4710

Answers (1)

Rene
Rene

Reputation: 10541

So lessons learned:

  • 1 post the original query not some faulty surrogate.
  • 2 post any error message you get.

The error message you get is:

ORA-38104: Columns referenced in the ON Clause cannot be updated: "A"."CUSTOMER_ID"

Solution: remove a.customer_id from the update clause.

merge into customers a
using (select 1 as customer_id
             ,'john' as customer_name
             ,'1' as position
             ,'01-JUL-15' as capture_date
             ,sysdate as modified_date
             ,'hello' as comments
         from dual) b
on (a.customer_id = b.customer_id)
when matched then
   update
      set a.customer_name = b.customer_name
         ,a.position      = b.position
         ,a.capture_date  = b.capture_date
         ,a.modified_date = b.modified_date
         ,a.comments      = b.comments
when not matched then
   insert
      (a.customer_id
      ,a.customer_name
      ,a.position
      ,a.capture_date
      ,a.modified_date
      ,a.comments)
   values
      (b.customer_id
      ,b.customer_name
      ,b.position
      ,b.capture_date
      ,b.modified_date
      ,b.comments)

Upvotes: 5

Related Questions