Multi-Row DML

A developer at work wanted a way to multi-row updates based on a set of values. The underlying model is a two-level tree. For example, a manager with one or more employees. Instead of having a series of transactions (insertions or deletions), a new list of employees is presented for the manager.

Deletions are easy: the NOT IN operator is used to remove rows no longer needed. The interesting problem is how to insert the new rows without incurring SQL errors.

Create the Test Table

Let’s create a table of many-to-many relationships between manager and employee, with the following external predicate:

memp( mgrno, empno ) := Employee #mgrno manages employee #empno.

CREATE TABLE memp (
  mgrno
    NUMBER
    CONSTRAINT memp_mgrno_nn
      NOT NULL,
  empno
    NUMBER
    CONSTRAINT memp_empno_nn
      NOT NULL,
  CONSTRAINT memp_pk
    PRIMARY KEY (
      mgrno,
      empno
    )
);

Load the Test Data

Tanel Poder has a way of Generating lots of rows using connect by – safely!. Let’s load some data into this table:

INSERT INTO memp( mgrno, empno )
  SELECT
      TRUNC( rownum / 10 ) + 1,
      rownum
    FROM
      dual
    CONNECT BY
      rownum < 50
;
COMMIT;

The Problem Restated

When we look at the data, we find that employee #1 manages the following set of employees:

{ 1, 2, 3, 4, 5, 6, 7, 8, 9 }

We want to have employee #1 to manage the following set of employees instead:

{ 6, 7, 8, 9, 50, 51, 52 }

In effect, five (5) rows are to be deleted ( 1, 2, 3, 4, 5 ), and three (3) inserted ( 50, 51, 52 ).

An Simple and Wrong Solution

A simple solution would be to delete all of the rows for employee #1 and insert the new set.

Unfortunately, foreign key constraints would impact this solution. If there are children, then the deletion of the parent has an impact especially if the parent is inserted after deletion. How do we maintain the child while the parent is re-incarnated?

An Esoteric Solution

My solution would use a DELETE with the NOT IN operator to remove rows not included in the new set, and to use the MERGE statement to insert the new rows, and the REGEXP_SUBSTR function:

DELETE FROM memp
  WHERE mgrno = 1
    AND empno NOT IN ( 6, 7, 8, 9, 50, 51, 52 );
MERGE INTO memp
  USING (
    SELECT
        1 AS mgrno,
        TO_NUMBER(
          REGEXP_SUBSTR( '6, 7, 8, 9, 50, 51, 52', '[^ ,]+', 1, rownum )
        ) AS empno
      FROM
        dual
      CONNECT BY
        rownum <= 7
    ) trn
  ON (
      memp.mgrno = trn.mgrno
    AND
      memp.empno = trn.empno
  )
  WHEN NOT MATCHED
    THEN INSERT (memp.mgrno, memp.empno )
    VALUES (trn.mgrno, trn.empno)
;
COMMIT;

The results are:

SQL> SELECT * FROM MEMP WHERE mgrno = 1;

     MGRNO      EMPNO
---------- ----------
         1          6
         1          7
         1          8
         1          9
         1         50
         1         51
         1         52

7 rows selected.

About these ads