Using MERGE to do an UPSERT in oracle

When doing straight JDBC coding, wouldn't it be nice if you could .merge() functionality even though you are not using ejb3? Recently, I discovered a way to do this using the Oracle statement "MERGE" to perform an "UPSERT" for me. UPSERT meaning please either update the existing record or create a new one for me.

The trick is using Oracle's MERGE command which is designed to merge records from 2 different tables. Since, I'm only inserting new data, I used a SELECT from dual to simulate selecting from a table. Then Oracle tries to see if an existing record is available based on the conditions specified in the "ON" for the "USING" statement.

This works perfect and is going into production on a system using Oracle 10g.

Enjoy, I've hand written a lot of UPSERT code in the past and it is never quite this elegant:

PreparedStatement statement = null;
try {
     statement = connection.prepareStatement("MERGE INTO ARTICLE a " +
                              "USING (SELECT ? title, ? author, ? body FROM dual) incoming " +
                              "ON (a.title = incoming.title AND a.author = incoming.author) " +
                              "WHEN MATCHED THEN " +
                              "UPDATE SET a.body = incoming.body " +
                              "WHEN NOT MATCHED THEN " +
                              "INSERT (a.title, a.author, a.body) " +
                              "VALUES (incoming.title, incoming.author, incoming.body)");

     statement.setString(1, "Title of Article");
     statement.setString(2, "Author Name");
     statement.setString(3, "A Cool Story from Last Night");
     statement.execute();
}
Comments:

Post a Comment:
Comments are closed for this entry.
About

Currently blogging about Apple, Rails and Java

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today