INCLUDE, UPDATE, and DELETE Processing

While MATCH and NEXT operations in MODIFY can operate on primary key or non-key columns and return single or multi-row answer sets, the MODIFY commands INCLUDE, UPDATE, and DELETE must always identify the target rows by their primary key. Therefore, in MODIFY, each update operation affects at most one row.

Example: Updating Rows With MODIFY Updating Rows With Maintain

In Maintain, the FOR phrase in the update command determines the number of rows affected.

Example: Updating Rows With MODIFY

Suppose you want to display all the employees in a department and increase certain salaries:

         MODIFY FILE EMPINFO
    CRTFORM LINE 1
      " PLEASE ENTER A VALID DEPARTMENT </1"
1.  " DEPARTMENT: <DEPARTMENT "
2.  MATCH DEPARTMENT
        ON NOMATCH REJECT
      ON MATCH CRTFORM LINE 10
3.    "EMP_ID: <D.EMP_ID   SALARY: <T.CURRENT_SALARY> "
4.    ON MATCH UPDATE CURRENT_SALARY 
      ON MATCH GOTO GETREST
    CASE GETREST
5.    NEXT EMP_ID
      ON NEXT CRTFORM LINE 10
      " EMP_ID: <D.EMP_ID   SALARY: <T.CURRENT_SALARY> "
      ON NEXT UPDATE CURRENT_SALARY
      ON NEXT GOTO GETREST
6.    ON NONEXT GOTO EXIT
    ENDCASE
    DATA
    END

The MODIFY procedure processes as follows:

1. The user enters the department (DEPARTMENT) for the search, PRODUCTION.

2. The MATCH command causes the RDBMS to search the table for the first row with the value PRODUCTION and return them in key sequence (EMP_ID). If none exists, the transaction is rejected.

3. If the supplied value matches a database value, the procedure displays it.

4. The procedure updates the salary field for the first retrieved row using the turnaround value from the CRTFORM. EMP_ID establishes the target row for the update.

5. Each time it executes the NEXT, the procedure retrieves the next row with the same department, PRODUCTION. It displays each one in EMP_ID order. It updates the salary field for each retrieved row with the turnaround value.

6. When no more rows exist for department PRODUCTION, the procedure ends.

The lines displayed by this MODIFY procedure follow:

 PLEASE ENTER A VALID DEPARTMENT
 
 DEPARTMENT:  PRODUCTION
 

 

EMP_ID:  071382660   SALARY:   11000.00

You can change the salary, or leave it as is. Each time you press Enter, the current salary is updated and the next employee ID displays:

EMP_ID:  119265415   SALARY:    9500.00
EMP_ID:  119329144   SALARY:   29700.00
EMP_ID:  123764317   SALARY:   26862.00
EMP_ID:  126724188   SALARY:   21120.00
EMP_ID:  451123478   SALARY:   16100.00

Example: Updating Rows With Maintain

In Maintain, you can use stack columns as turnaround values to update a table. The following annotated Maintain request named UPDATE1 updates the same rows as the preceding MODIFY request:

    MAINTAIN FILE EMPINFO
1.  WINFORM SHOW WIN1
2.  CASE MATCHREC
      FOR ALL NEXT EMP_ID INTO EMPSTACK WHERE DEPARTMENT EQ VALSTACK.DEPARTMENT
    ENDCASE
3.  CASE UPDSAL
      FOR ALL UPDATE CURRENT_SALARY FROM EMPSTACK
    ENDCASE
    END

The Maintain processes as follows:

1. A Winform named WIN1 opens. Assume that it displays an entry field labeled DEPARTMENT (whose source and destination stack is called VALSTACK) and a grid (scrollable table) with columns EMP_ID and CURRENT_SALARY. See your FOCUS documentation on maintaining databases for instructions on creating Winforms.

2. The user enters a DEPARTMENT value for the search and presses a PF key to invoke case MATCHREC. Case MATCHREC retrieves the rows that satisfy the NEXT criteria and stores them in a stack named EMPSTACK. The Winform displays the retrieved rows on the grid.

3. The user edits all the necessary salaries directly on the Winform grid and then presses a PF key to invoke case UPDSAL, which updates all salaries.

Executing the UPDATE1 procedure displays a Winform similar to the following: