Update Statement Oracle Tips by Laurent Schneider

 

Laurent Schneider is considered one of the top Oracle SQL experts, and he is the author of the book "Advanced SQL Programming" by Rampant TechPress.  The following is an excerpt from the book.

Update

Each measure can be updated with an UPDATEstatement within the model code block. Only the result is updated, so the table itself does not change.

SELECT
   *
FROM
   DEPT
JOIN
   EMP
USING
(
   DEPTNO
)
MODEL
RETURN UPDATED ROWS
DIMENSION BY
(
   EMP.ENAME
)
MEASURES
(
   DEPT.DNAME,
   EMP.SAL
)
(
   UPDATE DNAME['SCOTT']='FINANCE',
   UPDATE SAL['JONES']=SAL['SCOTT']
);

ENAME      DNAME                 SAL
---------- -------------- ----------
JONES      RESEARCH             3000
SCOTT      FINANCE              3000

The model is instructed to return only the updated rows. In the MODEL code, there are two rules: The department of Scott is set to FINANCE and the salary of Jones is set to the salary of Scott. Each individual measure can be retrieved and updated.

Upsert

Not only can MODEL update the existing values of the measures, but it can also generate new values for the dimensions.

SELECT
   *
FROM
   EMP
MODEL
RETURN UPDATED ROWS
DIMENSION BY
(
   ENAME,
   DEPTNO
)
MEASURES
(
   SAL
)
(
   UPSERT SAL['SCOTT', 20]=4000,
   UPSERT SAL['FRED', 40]=2900
);

 ENAME          DEPTNO        SAL
---------- ---------- ----------
SCOTT              20       4000
FRED               40       2900

The dimensions ENAME and DEPTNO uniquely identify the model. The UPSERT(UPDATE + INSERT) statement updates the salary of Scott to 2900 and inserts a salary of 2900 for the new employee Fred in the department 40. A SELECT statement never inserts a row in the table; instead, it adds a new row in the result set.

There is no INSERT statement in model. If the row exists, it will be updated. The default is UPSERT.

SELECT
   *
FROM
   DEPT
MODEL
DIMENSION BY
(
   DEPTNO
)
MEASURES
(
   DNAME
)
(
   DNAME[50]='FINANCE'
);

    DEPTNO DNAME
---------- --------------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS
        50 FINANCE

The department 50 is created.

Used with partition, UPSERT creates a new dimension in each partition:

SELECT
   *
FROM
   EMP
WHERE
   JOB='MANAGER'
MODEL
PARTITION BY
(
   DEPTNO
)
DIMENSION BY
(
   JOB
)
MEASURES
(
   ENAME,
   0 BONUS
)
(
   BONUS['MANAGER']=100,
   BONUS['ASSOCIATE']=250
);

    DEPTNO JOB        ENAME           BONUS
---------- ---------- ---------- ----------
        30 MANAGER    BLAKE             100
        30 ASSOCIATE                    250
        20 MANAGER    JONES             100
        20 ASSOCIATE                    250
        10 MANAGER    CLARK             100
        10 ASSOCIATE                    250

CV

The function CVreturns the current value of a dimension or a partition.

SELECT
   *
FROM
   EMP
MODEL
UNIQUE SINGLE REFERENCE
PARTITION BY
(
   DEPTNO
)
DIMENSION BY
(
   JOB
)
MEASURES
(
   ENAME,
   CAST(NULL AS VARCHAR2(30)) DESCRIPTION
)
RULES UPDATE
(
   DESCRIPTION['MANAGER']=CV(JOB)||' OF DEPARTMENT '||CV(DEPTNO),
   DESCRIPTION['SALESMAN']=CV(JOB),
   DESCRIPTION['PRESIDENT']=CV(JOB)||' '||ENAME[CV()]
)
ORDER BY
   DEPTNO;

   DEPTNO JOB       ENAME      DESCRIPTION
--------- --------- ---------- ---------------------------
       10 MANAGER   CLARK      MANAGER OF DEPARTMENT 10
       10 CLERK     MILLER
       10 PRESIDENT KING       PRESIDENT KING
       20 MANAGER   JONES      MANAGER OF DEPARTMENT 20
       20 ANALYST   FORD
       20 CLERK     SMITH
       20 CLERK     ADAMS
       20 ANALYST   SCOTT
       30 SALESMAN  TURNER     SALESMAN
       30 MANAGER   BLAKE      MANAGER OF DEPARTMENT 30
       30 SALESMAN  MARTIN     SALESMAN
       30 SALESMAN  WARD       SALESMAN
       30 CLERK     JAMES
       30 SALESMAN  ALLEN      SALESMAN

UNIQUE SINGLE REFERENCE has to be used when the dimension is not unique within a partition. In EMP, for instance, there is more than one clerk in department 20. The default UNIQUE DIMENSIONreturns an error if the dimension is not unique. When using SINGLE REFERENCE, duplicate values for a single reference cannot be accessed individually.

RULES UPDATESchange the default behavior from upsert to update in the model code. The description is a column initialized with the null string of type VARCHAR2(30). The model query fails if the measure datatype is too small to contain the assigned values. The description is assigned if the job is CLERK, SALESMAN or PRESIDENT.

When used as a dimension in ENAME[CV()], it is not mandatory to specify the argument to the CV function.

Conditions

Logical tests are applied on the dimensions to update more than one row at the same time:

SELECT
   *
FROM
   EMP
MODEL
RETURN UPDATED ROWS
DIMENSION BY
(
   EMPNO
)
MEASURES
(
   SAL
)
(
   SAL[EMPNO BETWEEN 7000 AND 7500]=1000
);
 

     EMPNO        SAL
---------- ----------
      7369       1000
      7499       1000

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     4 |    32 |     2   (0)| 00:00:01 |
|   1 |  SQL MODEL ORDERED           |        |     4 |    32 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |     4 |    32 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | PK_EMP |     4 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("EMPNO">=7000 AND "EMPNO"<=7500)

MODELupdates the salaries of the employees between 7000 and 7500. It is interesting to note that the optimizer chooses an index range scan to access those two rows. That is, the model code is analyzed before the execution and the predicates are pushed to the main query.

In addition to the standard SQL conditions, the dimension IS ANYis a condition that always returns true:

SELECT
   *
FROM
   EMP
MODEL
DIMENSION BY
(
   DEPTNO,
   EMPNO
)
MEASURES
(
   COMM
)
(
   COMM[DEPTNO IN (10,20), EMPNO IS ANY]=1000,
   COMM[30,ANY]=2000
)
ORDER BY
   DEPTNO;
 

    DEPTNO      EMPNO       COMM
---------- ---------- ----------
        10       7782       1000
        10       7839       1000
        10       7934       1000
        20       7566       1000
        20       7369       1000
        20       7876       1000
        20       7788       1000
        20       7902       1000
        30       7499       2000
        30       7654       2000
        30       7900       2000
        30       7844       2000
        30       7521       2000
        30       7698       2000

In departments 10 and 20, each employee receives a 1000 commission. In department 30, the commission is 2000. Both syntaxes are equivalent.

Another syntax specific to model is the cell IS PRESENTcondition. The IS PRESENT condition checks if the cell is present in the base query.

SELECT
   *
FROM
   DEPT
MODEL
DIMENSION BY
(
   DEPTNO
)
MEASURES
(
   DNAME
)
(
   DNAME[50]='FINANCE',
   DNAME[ANY]=
      CASE
         WHEN DNAME[CV()] IS PRESENT
         THEN LOWER(DNAME[CV()])
         ELSE UPPER(DNAME[CV()])
      END
);

    DEPTNO DNAME
---------- --------------
        10 accounting
        20 research
        30 sales
        40 operations
        50 FINANCE

The departments 10 to 40 are present in the base query. They are converted to lower case. The department 50 is not present in DEPT; it is uppercased.