Insert if not exist and update certain values if it does

Question

I'm using JDBI3 (and would like to use @SQLUpdate) and an Oracle DB.

I want to insert an item with 4 columns into the table if it does not exist, if it does exist I want to instead update 3 of the 4 values of the item. If it wasn't Oracle I would've used some ON DUPLICATE_KEY logic but that does not exist in Oracle. I read some things about using Merge but the Queries seemed really wonky for what I was trying to do. Any tips on what to look for?

Additional question: If it is Merge I should use (with some form of sub queries I assume), how does the query affect performance? I think this database is quite write heavy.

MERGE INTO device db USING (SELECT 'abc' AS col1, 'bcd' as col2, 'cde' as col3, 'def' as col4 FROM DUAL) input
on (db.col1 = input.col1 AND db.col2= input.col2)
WHEN MATCHED THEN UPDATE
SET db.col4 = input.col4
WHEN NOT MATCHED THEN INSERT
(db.col1, db.col2, db.col3, db.col4)
VALUES (input.col1, input.col2, input.col3, input.col4)

Try the operation that promises success first (i.e. try update if the row is usually there, else try insert). If the statement fails (either dup key after attempted insert, else no rows updated), catch the error/warning and try the other one. (DB systems don't abort transactions just because one statement ran into an error). — Ronald
– Ronald, Commented Nov 23, 2021 at 10:53

Littlefoot · Accepted Answer · 2021-11-23 11:08:13Z

1

Merge it is. Performs well.

Dummy example based on your description:

merge into target_table a
  using source_table b
  on (a.id = b.id)
when matched then update set
  a.name = b.name,
  a.job  = b.job,
  a.sal  = b.sal
when not matched then 
  insert (id, name, job, sal) 
  values (b.id, b.name, b.job, b.sal);

answered Nov 23, 2021 at 11:08

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Zanndorin Over a year ago

So about those target/source table. You create a temporary table with the comparison query? In this case USING (SELECT FIELD FROM TABLE WHERE ID = inputID and OTHERFIELD != NULL) ?

Littlefoot Over a year ago

Yes, it can be a subquery, no problem. BTW, when you're comparing something to NULL, in Oracle it is otherfield IS NOT NULL, not != null.

Littlefoot Over a year ago

Which error do you get?

Zanndorin Over a year ago

We fixed it it was the timezone and human error. thanks man.

Littlefoot Over a year ago

You're welcome; I'm glad you made it.

|

Collectives™ on Stack Overflow

Insert if not exist and update certain values if it does

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related