- Associated Updates and Deletes

Associated Updates

[EE] Creating an associated update is very similar to creating an associated insert, but instead of inserting a row with values from an associated row, it updates a row with values from an associated row.

Creating an Associated Update is very similar to doing basic 1- 1 or 1-many associations (described in at the beginning of this section). To create an association operation, begin by creating a new operation as described in the Operations chapter. Next, change the Operation Type from SQL to either the 1-1 Association or 1-Many Association Type. Then you need to set its operation code. After the operation code has been set, you need to modify a Field’s Field Descriptor to set the association, so when field is generated it will have a link to update in its associated row or rows. This process is described below:

SQL Associated Update Example

The operation code needed for an SQL Associated Update is very similar to the SQL statement for a normal update (described in the Update chapter), with some changes for the association.

Here is an example for a 1-1 Associated Update: after selecting the Create Operation from the TOC, select 1-1 for Association Type. The Operation Code is the SQL statement for updating the database. In our example we will be updating the SALARY field in the EMPLOYEE table by adding 1000 to the original value, linking from the Department Table. (We want to update all salaries for a single department only.) The code shown below will link the Department table to the Employee table allowing you to update entries in the Employee table with the similar fields WORKDEPT and DEPTNO:

The operation code used for the Update:

UPDATE PJDATA.EMPLOYEE SET SALARY = SALARY + 1000 WHERE WORKDEPT = ??DEPTNO

Notice the SQL code is similar to UPDATE SQL statement described in the Update chapter. In this example, the PJDATA.EMPLOYEE table is being updated. The Set clause sets the salary equal to the current salary plus 1000. The WHERE clause, shows the which field the association is being updated from, in this example the WORKDEPT field in the EMPLOYEE is being linked with DEPTNO field which is the DEPARTMENT table. The linking is done with double question marks (??) and the name of the field from the associated row which is used in the update. After creating the associated update you will see it in your list of operations but will not see it your application. (This is because the associated update cannot be directly run, it must be invoked after the associated row has been retrieved.)

Now you have to assign your association to a specific Field - in our example this is the DEPTNO field. To do this run an operation that displays the associated table (in our case the DEPARTMENT table, shown below).

Once you have a screen similar to the one above (without the hyperlinks) you can set up the association you previously created. To do this click the gear symbol next to the field you want along with your association. In the Field Descriptor Screen scroll down to the Advanced Settings area (shown below) and set the Association Operation to your previously created Operation.

Once you have saved your changes to the field descriptor, the association is complete. Now, whenever the DEPTNO field of the Department table is displayed, it will have a hyperlink to increment by 1000 the SALARY field of the associated rows in the EMPLOYEES table.

Associated Deletes

[EE] Associated deletes allow you to delete one or more rows based on the values contained in an associated row. Creating an Associated Update is very similar to creating basic 1- 1 or 1-many associations (described in the beginning of this section). To create an operation for doing associated deletes, you first create a new operation and set its Operation Type from SQL to either 1-1 Association or 1-Many Association Type. Then you set its operation code to do the actual delete; finally you attach the operation to an associated field by editing that field’s field descriptor. This process is described in detail below:

SQL Associated Delete Example

The operation code needed for the SQL Associated Delete is very similar to a normal SQL DELETE statement, with some changes for the Association. Our example will deal with a 1-Many Associated delete. After selecting the Create Operation from the TOC, choose 1-Many for Association Type and set the Operation Code to the SQL for performing the delete. The code shown below will link the DEPARTMENT table to the EMPLOYEE table allowing you to delete entries in the EMPLOYEE table whose WORKDEPT field matches the DEPTNO field of a row in the DEPARTMENT table:

The operation code used for the Associated Delete:

DELETE FROM PJDATA.EMPLOYEE WHERE WORKDEPT = ??DEPTNO

Notice the SQL code is similar to DELETE SQL statement described in the SQL Delete chapter. The WHERE clause links WORKDEPT field in the EMPLOYEE table to the DEPTNO field in the DEPARTMENT table. After creating the Associated Delete you will see it in your list of operations but will not see it your application. (This is because the operation can only be run after an associate row has been displayed.) The next step is to assign your association to a specific field in the associated table (DEPTNO). To do this run an operation that displays the associated table:

Once you have a screen similar to the one above (without the hyperlinks) you can set up the association you previously created. To do this click the gear symbol next to the field you want along with your association. In the Field Descriptor Screen scroll down to the Advanced Settings area (shown below) and set the Association Operation to your previously created Operation.

Once you have saved your changes to the field descriptor, the association is complete. Now, whenever the DEPTNO field of the Department table is displayed, it will have a hyperlink to increment by 1000 the SALARY field of the associated rows in the EMPLOYEES table.