You can use this Loader command to control the update of individual column values in tables.
The target table exists in the database instance.
<dataupdate_command> ::=
DATAUPDATE TABLE <table_spec>
<acc_column_spec_mlt> <set_column_spec_mlt> <instream_spec>
[<longfile_spec_mlt>]
<acc_column_spec_mlt> ::=
<acc_column_spec> <acc_column_spec_mlt>
<acc_column_spec> ::= <key_column_spec> |
<simple_column_spec>
<key_column_spec> ::= KEY <simple_column_spec>
<simple_column_spec> ::= <column_descriptor>
| <column_assignment>
<set_column_spec_mlt> ::=
<set_column_spec> <set_column_spec_mlt>
<set_column_spec> ::= SET <load_column_spec>
You can also use the keywords UPDATE DATA instead of the keyword DATAUPDATE.
The Loader generates an internal UPDATE statement from the DATAUPDATE command and then executes it.
You use the syntax rule acc_column_spec_mlt to define the qualification columns.
The qualification columns must be listed before the target columns (set_column_spec_mlt) in a DATAUPDATE command.
You can use both key columns and non-key columns as qualification columns.
If you use key columns (key_column_spec) as qualification columns, each data record in the data stream updates the row in the target table that matches the specified key.
DATAUPDATE TABLE customer
KEY cno 01-05
SET address 06-30
INSTREAM 'customer.data' FORMATTED
If you use non-key columns (simple_column_spec) as qualification columns, each data record in the data stream updates each row in the target table that is identified with the value(s) of the qualification column(s). You therefore use the syntax rule simple_column_spec to assign the data fields of the qualification columns in the data stream to the columns in the target table and can specify the external data types.
DATAUPDATE TABLE customer
address 1
SET zip 2
INSTREAM 'customer.data'
DATAUPDATE TABLE customer
name 01-10
firstname 11-20
SET zip 21-25
SET address 26-50
INSTREAM 'customer.data' FORMATTED
You use the syntax rule set_column_spec_mlt to define the columns in the target table that are to be updated using this command.
DATAUPDATE TABLE customer
KEY cno 01-05
SET zip 06-09
INSTREAM 'customer.data' FORMATTED
The individual row values in the specified table columns are updated. All the changes made to the target table(s) as a result have also been written to the log area of the database instance.
If errors occur while a DATAUPDATE command is being executed, the Loader terminates the process and displays an error message. A log file is written.