In previous versions, when using the For Each command to update data, the generated code updated each record through a SELECT / DO WHILE structure. As from 9.0 version, this pattern is identified and generated as an UPDATE structure. This greatly improves performance because all updates are done directly with one sentence instead of one update per row.
When the following mass update pattern is identified:
For Each Where <Cond>
Att = <Expression>
Att = <Expression>
an "UPDATE where <cond>" is generated.
It can include attributes, variables or constants. All the assigned (updated) attributes must belong to the same table.
The condition can only include attributes that belong to the updated table. Certain types of functions can be included in the condition.
Note: no other commands, such as calls, IFs, when, etc., can be included within the FOR EACH group. Only assignments to attributes are admitted (optimized).
When the massive update pattern is identified, the navigation shows it as follows:
For Each EMPLOYEE (Line: 3)
It indicates that the pattern has been identified and that an Optimized Update has been generated.
1) The following procedure increases all employees' salaries by 10%.
2) The following procedure increases all employees' salaries by 10%, too. The only variant that it presents with respect to the previous one, is that it uses a variable instead of using the fixed value directly.
3) The following procedure increases by 10% the salaries of all employees between 30 and 40 years old. That is to say that in this case there are also filters defined (where clause).
where EmployeeAge>=30 and EmployeeAge<=40
4) The following procedure increases by 10% the salaries of all employees whose name start with "C". Notice that it is being used a Function within the where clause.
where substr(EmployeeName,1,1) = "C"
Note: <Expression> cannot be a formula.
Comments & Collaboration
Compatibility issues could appear depending on the <Expression> and <Att> datatype definition. See related SAC.
Language: Java, .NET, .NET Mobile
DBMS: DB2 UDB, Informix, Oracle, MS SQL Server, MySQL, PostgreSQL.
Server Side Functions/Methods