About Updating Tables | Teradata Vantage - Updating Tables - Analytics Database - Teradata Vantage

Frequently, when updating the database, you make the same change to many rows. It is helpful to eliminate the repetitive work required to update them individually. The REPEAT command serves this purpose.

Updating Rows Using a Single Request

If you can describe all of the rows by some formula, then you can use a single request to update them all. 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 all of the rows by a formula, you must describe them 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;

This approach is convenient for a few updates, but if there were hundreds, the script would become very long. It would also be very inflexible; you would have to edit each entry to use the script again for another list of employees.

Updating Rows by Importing a File

A better approach would be 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.