PL/SQL MERGE Query ( UPSERT )

One of my recent findings about PL/SQL is the MERGE query, also known as an UPSERT. This is exactly what it sounds like, an INSERT and UPDATE all in one block of code. This can be really useful and can really reduce the amount of code you have to write to perform certain operations. On top of reducing the amount of code, it can really make your code more readable and easier to maintain.

Basic MERGE Syntax

MERGE INTO destination_table
USING (SELECT key_column, value_column FROM source_table)
ON (destination_table.key_column = source_table.key_column)
WHEN MATCHED THEN
 UPDATE SET destination_table.value_column = source_table.value_column
WHEN NOT MATCHED THEN
 INSERT (destination_table.key_column, destination_table.value_column)
 VALUES (source_table.key_column, destination_table.value_column);

Looking at the basic syntax above, you get an idea of how the MERGE query works. You can make this as basic or as complicated as you would like. Of course, you can use JOINS, VIEWS, etc. in either the source or destination queries. You can leave off either of the WHEN MATCHED THEN or WHEN NOT MATCHED THEN blocks if you don't want to perform operations in those certain situations.

Let's take a look at an example scenario where the MERGE query could be used. In our example, we have two tables. In one table, we maintain a list of widgets and in another table we keep the properties for those widgets. The tables with some sample data is listed below.

ID	WIDGET
1	Super Widget
2	Awesome Widget

WIDGET_ID	PROP_NAME	PROP_VALUE
1	COLOR	GREEN
1	SIZE	LARGE
2	SIZE	MEDIUM

In our WIDGET table, we have two widgets. The Awesome Widget and the Super Widget. In our WIDGET_PROPERTY table, we have two properties for the Awesome Widget, size and color. We only have one property for the Super Widget, which is size. Management has decided that all widgets will now come in the color blue. So we need to have a color property for all widgets and the value must be blue.

This could get pretty complicated since some of the widgets already have a color property and we can't add another color property to a given widget. So a simple INSERT won't work unless we delete all color properties first, which isn't very efficient, especially if there is a lot of data.

We could use an UPDATE query for the ones that already exist, but not all widgets have a color property, so this will still not solve that problem. With a MERGE query, we can solve this problem with a single statment in PL/SQL. Here is the PL/SQL code we would use to handle this situation.

MERGE INTO WIDGET_PROPERTY DESTINATION
USING ( SELECT WIDGET.ID, 'COLOR' AS PROP_NAME FROM WIDGET ) SOURCE
ON ( DESTINATION.WIDGET_ID = SOURCE.ID AND
    DESTINATION.PROP_NAME = SOURCE.PROP_NAME )
WHEN MATCHED THEN
  UPDATE SET DESTINATION.PROP_VALUE = 'BLUE'
WHEN NOT MATCHED THEN
  INSERT ( DESTINATION.WIDGET_ID, DESTINATION.PROP_NAME, DESTINATION.PROP_VALUE )
  VALUES( SOURCE.ID, SOURCE.PROP_NAME, 'BLUE' );