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();
}

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: