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, Nov 23 '21 at 10:53

score 1 · Accepted Answer · answered Nov 23 '21 at 11:08

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 '21 at 11:08

Littlefoot

131,892
15
35
57

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)` ? – Zanndorin Nov 23 '21 at 12:16
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 Nov 23 '21 at 12:24
Which error do you get? – Littlefoot Nov 23 '21 at 12:54
We fixed it it was the timezone and human error. thanks man. – Zanndorin Nov 23 '21 at 13:15
You're welcome; I'm glad you made it. – Littlefoot Nov 23 '21 at 13:22
I was too happy too fast, The insert never triggers and always gives 0 rows merged :D :( – Zanndorin Nov 23 '21 at 13:48
INSERT runs when columns you mention in ON clause don't match. If it (the INSERT) never triggers, it means that there's always a *match* so - there's nothing to insert. To test it, delete a row from the TARGET_TABLE (don't COMMIT so that you could ROLLBACK afterwards) and then run MERGE. – Littlefoot Nov 23 '21 at 13:51
We are trying to find the case where column X is null or not null and depending on that we either want to insert a whole row or just update column Y – Zanndorin Nov 23 '21 at 13:58
Ok we made it finally, Oracle Syntax is werid... Will update question, – Zanndorin Nov 23 '21 at 14:36

Insert if not exist and update certain values if it does

1 Answers1