Frequently, when updating the database, you make the same change to multiple rows. The REPEAT command lets you eliminate the repetitive work required to update the rows individually.
Updating Rows Using a Single Request
If you can describe multiple rows by a formula, you can use a single request to update those rows. The following request, for example, updates the salaries of all employees in department 600 of the database Personnel:
update personnel.employee set salary=salary*1.07 where deptno=600;
Updating Rows Using Multiple Requests
If you cannot describe multiple rows by a formula, you must update the rows individually. The following requests, for example, update the salaries of two employees:
update personnel.employee set salary=salary*1.07 where empno = 10006;
update personnel.employee set salary=salary*1.07 where empno = 10013;
For hundreds of rows, the script becomes long and inflexible. You must edit each entry to use the script again for another list of employees.
Updating Rows by Importing a File
A better approach is to create a separate file (for example, RAISEEMP) containing two records in data format representing the employee numbers 10006 and 10013. Then, use the BTEQ IMPORT command with a REPEAT command to update the rows in the database.
Using the REPEAT Command When Importing a File
The REPEAT command appears before the request and specifies the total number of requests to be submitted.
The following BTEQ script using the BTEQ REPEAT command opens the character-format file RAISEEMP and repeats the update sequence twice:
.IMPORT data file=raiseemp .REPEAT 2 using enumb (char(5)) update personnel.employee set salary=salary*1.07 where empno = :enumb;
For each employee, BTEQ reads a value from the RAISEEMP file for the variable called enumb and carries out the UPDATE for the row whose EmpNo equals the value of enumb.