The following notes describe how to use an UPDATE statement following a DML command. An UPDATE statement may also be used in the support environment; normal rules for UPDATE are followed in that case.
- To update records in a table, the userid that is logged on must have UPDATE privilege for the table.
- In an IMPORT task, if multiple Unique Primary Index (UPI) columns are specified, the columns should all be specified in the WHERE clause of the UPDATE statement. In this case, the WHERE clause is fully qualified, thereby allowing Teradata TPump to avoid table locks and optimize the processing.
- For Teradata TPump use, if the object of the UPDATE statement is a view, it must not specify a join. Teradata TPump operates only on single table statements so UPDATE statements must not contain any joins.
- Only one object may be identified.
- The OR construct can be used in the WHERE clause of a DELETE statement; alternatively, two or more separate DML statements (one per OR term) can be used, with the DML statements applied conditionally with the APPLY clause of the IMPORT command. The nature of the alternatives will usually make one of the methods more appropriate.
- The maximum number of INSERT, UPDATE, and DELETE statements that can be referenced in an IMPORT is 128. The 128th DML which would cause the insertion of the DML sequence number of 128 for the DMLSEQ field in the error table could lead to database 3520 error.
- The maximum number of DML statements that can be packed into a request is 1500. The default number of statements packed is 20.
- To ensure data integrity, the SERIALIZE parameter defaults to ON in the absence of an explicit value if there are upserts in the Teradata TPump job.