Oracle MERGE: Elegantly updating KVP lookups in a database

Storing key-value-pair (KVP) look-up information in a database is pretty routine stuff. There’s normally a simple table with a key and a value, as you would expect. Very occasionally the information stored in this look-up table will need updating.

Often database deployment scripts adding or amending the look-up data will be simple text files containing the following type of code excerpt.

INSERT INTO kvpTable(key, value) VALUES('DebugMode', 'Yes');
INSERT INTO kvpTable(key, value) VALUES('ApplicationVersion', '3.4.3');

This works fine, as long as the keys are not already present (from a previous execution or application deployment and a PRIMARY KEY constraint on column key), and then the scripts rejected by the DBA.

Variants on the theme accommodating for a bit of error trapping include the grotesque block, as shown below, repeated for each key.

DECLARE
  BEGIN
    UPDATE kvpTable
      SET value='3.4.3'
        WHERE key = 'ApplicationVersion';
    IF SQL%ROWCOUNT = 0 THEN
      INSERT INTO kvpTable(key, value)
         VALUES('ApplicationVersion', '3.4.3');
    END IF;
  END;
/

I see this pattern a lot. I occasionally see code written by others containing an INSERT, with an EXCEPTION WHEN OTHERS clause to do the UPDATE as well; basically it is non-maintainable boilerplate yet handcrafted code.

For some reason, the Oracle MERGE (UPSERT) is infrequently used for the task, yet it seems so matched for the purpose.

I disclose an example below demonstrating its use in this model scenario.

 SQL>
 SQL>
 SQL>
 SQL>
 SQL> CREATE TABLE kvpTable AS
 2 SELECT 'ApplicationVersion' Key, '3.4.2' Value
 3 FROM DUAL;
Table created.

SQL> ALTER TABLE kvpTable
2      ADD CONSTRAINT pk_kvp PRIMARY KEY(key); 
Table altered.

SQL>
SQL>
SQL> SELECT *
2 FROM kvpTable;

KEY                  VALUE
-------------------- ----------
ApplicationVersion   3.4.2

SQL>
SQL>
SQL>
SQL> MERGE INTO kvpTable s
2     USING (
3                  SELECT 'ApplicationVersion' key, '3.4.3' value FROM DUAL
4            UNION
5                  SELECT 'DebugMode' key, 'Yes' value FROM DUAL
6           ) t
7        ON (s.key = t.key)
8     WHEN NOT MATCHED
9        THEN INSERT(key, value) VALUES(t.key, t.value)
10    WHEN MATCHED
11       THEN UPDATE SET s.value = t.value;

2 rows merged.

SQL>
SQL> SELECT *
2 FROM kvpTable;

KEY                  VALUE
-------------------- ----------
ApplicationVersion   3.4.3
DebugMode            Yes

SQL>
SQL>
SQL>

Note there is no error trapping, there are no discrete different SQL statements for INSERT and UPDATE, the script can be executed over and over without side effects nor having to tear things down between repeated executions of the script.

Importantly, this trivial code snippet is also elegant, bullet-proof, and you can understand at-a-glance what is going on.

The Oracle MERGE statement was introduced in Oracle 9 (way back in 2001, so there’s really no excuse to not be familiar with it) and has undergone considerable enrichment in functionality ever since.

The code was developed against

SQL>
SQL>
SQL>
SQL> SELECT *
2 FROM v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL>

— Published by Mike, 08:18:26 16 September 2016