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

Database Administration

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-11-03
dita:mapPath
pgf1628096104492.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
ujp1472240543947
lifecycle
latest
Product Category
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.

When using a file originally exported with the BTEQ EXPORT command as the source for an IMPORT command across a different platform type, ensure that the endianness type of both platforms is the same. You can verify this from the “Client Platform Byte Order” tab in the output of the SHOW CONTROLS command.