Oracle MERGE: Elegantly updating to a sequential version number

It is often important to have a sequential version number (or other value such a credit note or invoice number) associated with piece of information retained within a database. Often ‘holes’ in the sequence values exist, however.

In the following trivial data set, there exist a few ‘holes’, namely the first version number of articleID ‘c’ is 2, the first version number of articleID ‘d’ is also ‘2’, and the second version number ‘6’. Perhaps the missing version numbers were unpublished drafts, or deleted.

mike@ora11.2sol sql>
mike@ora11.2sol sql>
mike@ora11.2sol sql> SELECT articleID, versionNumber, logMessage 
2                      FROM tblTest
3                        ORDER BY articleID ASC, versionNumber ASC;

ARTICLEID  VERSIONNUMBER LOGMESSAGE
---------- ------------- --------------------------------------------- 
a          1             updated 24/09/2016 10:15 msmith
a          2             updated 24/09/2016 16:18 msmith
a          3             approved 24/09/2016 16:19 msmith
b          1             updated 01/09/2016 16:15 moshea6561
c          2             updated 17/09/2016 21:45 johnf
d          2             updated 15/04/2016 10:42 b.johnson
d          6             updated 03/05/2016 11:15 \\obig\gordonf12

7 rows selected.

mike@ora11.2sol sql>
mike@ora11.2sol sql>

How can the version numbers be efficiently and elegantly renumbered, so the earliest version number is renumbered ‘1’, the second ‘2’, and so on for each articleID?

The answer, yet again, is the underused MERGE statement in Oracle, where, in my example, it is combined with a liberal sprinkling of Analytic Functions to ensure the desired (version number) ordering.

mike@ora11.2sol sql>
mike@ora11.2sol sql>
mike@ora11.2sol sql>
mike@ora11.2sol sql>
mike@ora11.2sol sql> MERGE INTO tblTest f0
  2                    USING (SELECT rowid rid, articleID, versionNumber,
  3                             ROW_NUMBER() OVER (PARTITION BY articleID ORDER BY versionNumber ASC) rn,
  4                             COUNT(*) OVER (PARTITION BY articleID) c,
  5                             MAX(versionNumber) OVER (PARTITION BY articleID) m
  6                               FROM tblTest
  7                          ) f1
  8                     ON (f0.rowid = f1.rowid AND f1.c != f1.m)
  9                       WHEN MATCHED THEN UPDATE SET versionNumber = rn;

3 rows merged.

mike@ora11.2sol sql>
mike@ora11.2sol sql> SELECT articleID, versionNumber, logMessage
2                      FROM tblTest
3                        ORDER BY articleID ASC, versionNumber ASC;

ARTICLEID  VERSIONNUMBER LOGMESSAGE
---------- ------------- ---------------------------------------------
a          1             updated 24/09/2016 10:15 msmith
a          2             updated 24/09/2016 16:18 msmith
a          3             approved 24/09/2016 16:19 smith
b          1             updated 01/09/2016 16:15 moshea6561
c          1             updated 17/09/2016 21:45 johnf
d          1             updated 15/04/2016 10:42 b.johnson
d          2             updated 03/05/2016 11:15 \\obig\gordonf12

7 rows selected.

mike@ora11.2sol sql>

Now isn’t that just fine?

— Published by Mike, 12:03:26 24 September 2016