MySQL upsert, Oracle merge

How many times have you seen this “pattern”?

unsigned int row_count = foo->update();
if (row_count == 0) {
   foo->insert();
}

Wouldn’t it be nice if you could write all that in a single line? Say, something like

foo->update_or_insert_if_it_doesnt_exists();

Well, good news, you can! Obviously it’s not standard SQL, nothing useful ever is, but even so I think using an upsert (who comes up with those names?) can be quite good for your health.

So, how do you use it? It’s easy;

INSERT INTO Table ( col1, col2 )
SELECT 'a', 'b'
ON DUPLICATE KEY UPDATE col1 = 'a', col2 = 'b';

Go on, try it, I’ll wait. What? It didn’t work? Oh, I forgot, you need to create a unique key so the engine can recognize when there is a duplicate key (say, ‘create index unique on col1’). Try it now.

Nice, isn’t it? Oracle has its own version of upsert, called merge (at least the name is better) but it itches a little bit when I write about Oracle, so go and check this page instead.

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