How to write "INSERT IF EXISTS UPDATE" in Oracle using JAVA

Question

I have a ERROR_MSG table which stores error messages with some ids. I want to insert error message if id is not present in table and if its present update error message. Inserting using below java JDBC code.

ID ERROR_MSG
1  ERR1
2  ERR2
3  ERR3

This is my code:

insertQry = "SQL";
Connection con = null;
PreparedStatement stmt = null;
try {
    con = getDataSource().getConnection();
    stmt = con.prepareStatement(insertQry);
    for(ListingAckNackData errorList: listOfListingERROR) {
        stmt.setLong(1, eqGlobalData.getSrcMsgId());
        stmt.setString(2, errorList.getGliId());
        if (null != errorList.getListingRevisionNo()) {
            stmt.setInt(3, errorList.getListingRevisionNo());
        } else {
            stmt.setNull(3, Types.NULL);
        }
        if (null != errorList.getErrorMessage()) {
            stmt.setString(4, errorList.getErrorMessage());
        } else {
            stmt.setNull(4, Types.NULL);
        }
        stmt.addBatch();
    }
    stmt.executeBatch();
}

Possible duplicate of [Oracle: how to UPSERT (update or insert into a table?)](https://stackoverflow.com/questions/237327/oracle-how-to-upsert-update-or-insert-into-a-table), because I don't believe this is possible in a simple `INSERT OR UPDATE` query with Oracle. — AxelH, Dec 11 '17 at 07:47
Note that this [answer](https://stackoverflow.com/a/4015306/4391450) provide a simpler solution in Java but will cost some processing... — AxelH, Dec 11 '17 at 07:49
UPDATE tablename SET val1 = in_val1, val2 = in_val2 WHERE val3 = in_val3; IF ( sql%rowcount = 0 ) THEN INSERT INTO tablename VALUES (in_val1, in_val2, in_val3); END IF; how to write this in string insertQry for java code above. — Arpan Paliwal, Dec 11 '17 at 08:38
You should consider using named parameters like :paramName to prevent mixed parameter ordering. — leonardo12, Dec 19 '17 at 16:33

score 1 · Accepted Answer · answered Dec 11 '17 at 08:47

1

The simplest solution in JAVA is to check if the row exist.

You start by getting a row count for the specific id you want to insert/update

select count('a') as rowExist from table where id = ?

Then, based on the result, you can easily create your query

if(rowExist > 0){
    query = "update ..";
else
    query = "insert ...";

Note that the parameters are probably not in the same order as you expect, you need to create the insert in the correct order to have the id at the end (since update need a where clause)

insert into Table (name, birthday, id) values (?, ?, ?)
update Table set name = ?, birthday = ? where id = ?

answered Dec 11 '17 at 08:47

AxelH

14,325
2
25
55

1

For that two sql are needed, i want it in single sql, it is not possible in ORACLE ? – Arpan Paliwal Dec 11 '17 at 09:01
@ArpanPaliwal It is possible if you use a stored procedure that you called from Java using a `Callable`. For that, check the duplicate. There is no need to rewrite the solution here, since everything will be done in a store proc on the DB. You just need to create a `Callable` to call that procedure. – AxelH Dec 11 '17 at 09:04
can't use store proc for a small change thanks for above solution. – Arpan Paliwal Dec 11 '17 at 09:11
With this solution you have to think about transaction handling. Otherwise multiple threads may insert duplicates or run into exceptions if a matching constraint exists. – leonardo12 Dec 19 '17 at 16:29
Isn't having a single `MERGE` statement that handles both better than having two hits on database? – Ayush Kumar Aug 14 '20 at 10:17
I wasn't aware of the MERGE, it seems most efficient as everything is done in one call, but this is providing a lot of information, so a bit more messy in the prepared statement in JAVA. As an example with [this question](https://stackoverflow.com/questions/17705576/oracle-merge-and-prepared-statement) using a merge. Thanks for the knowldge @AyushKumar – AxelH Aug 25 '20 at 12:18

leonardo12 · Answer 2 · 2017-12-19T16:40:50.420

0

It is possible to run a database statement as questioned. Simply use SQL command MERGE INTO... IF NOT MATCHED INSERT... IF MATCHED UPDATE ... You will find an full example and documentation here.

edited Dec 19 '17 at 16:40

answered Dec 19 '17 at 16:26

leonardo12

398
1
7
18

How to write "INSERT IF EXISTS UPDATE" in Oracle using JAVA

2 Answers2