UPDATE COLUMNS 
You can use the UPDATE COLUMNS command to change individual column values of a table. The table specified must be available in the database.
Prerequisites
You can use all of the demo data in database DEMODB. Start the Loader and log onto demo database DEMODB as database administrator MONA with the password RED.
Use the session mode: loadercli -d demodb -u mona,red
Activities
The data of the CUSTOMER table in the HOTEL schema is to be changed. After the keyword TABLE you can specify the table and column description. The column definition of the target columns occurs after the keyword SET. You can make entries after the keyword DATA to control which new column values are to be set.
Updating Values in a Table Column
You can update the values of columns of a table.
UPDATE COLUMNS TABLE hotel.customer
cno 1
SET address 5
DATA INSTREAM 'customer_csv.data'
The application data (DATA) is imported in CSV format (default value).
The CNO column is the qualification column. In the CUSTOMER table, rows for updating a column value are selected that are qualified by the CNO values specified in customer_csv.data. The ADDRESS values in each of these rows are replaced by the ADDRESS values specified in customer_csv.data.
UPDATE COLUMNS TABLE hotel.customer
cno 1-5
SET address 76-116
DATA INSTREAM 'customer_fwv.data' FWV
The application data (DATA) is imported in FWV format.
UPDATE COLUMNS TABLE hotel.customer
cno 1-5
SET address 76-116
DATA INSTREAM 'customer_fwvbinary.data' FWV BIMARY
The application data (DATA) is imported in FWV BINARY format.
See also: Loader, Data Stream, CSV, FWV, FWV BINARY
Updating Values in More than One Column of a Table
You can update the values of more than one column in a table.
UPDATE COLUMNS TABLE hotel.customer
cno 1
SET firstname 'Secret'
SET address 5
DATA INSTREAM 'customer_csv.data'
The CNO column is the qualification column. In the CUSTOMER table, rows for updating a column value are selected that are qualified by the CNO values specified in customer_csv.data. The FIRSTNAME values in each of these rows are replaced by the value Secret. The ADDRESS values in each of these rows are replaced by the ADDRESS values specified in customer_csv.data.
if_condition
You can select individual data records from the DATA data stream and then update these records only in the specified table. Note the sequence of the syntax elements. Enter the IF condition first.
UPDATE COLUMNS TABLE hotel.customer
IF POS 1 > '4000'
cno 1
SET address 5
DATA INSTREAM 'customer_csv.data'
The rows that have a value above 4000 in the CNO column are updated in the CUSTOMER table.
Handling LOB Values
The data of the HOTEL table in the HOTEL schema is to be updated. The HOTEL table has the LOB column INFO. Enter the LOB syntax element after the DATA syntax element.
UPDATE COLUMNS TABLE hotel.hotel
hno 1
SET name 2
SET info 5
DATA INSTREAM 'hotel_csv.data'
LOB INSTREAM INFO 'hotel_info_csv.lob'
The NAME and INFO columns in the HOTEL table are updated. The LOB values are stored in the data stream hotel_info_csv.lob
See also: Loader, Exporting and Importing LOB Values
Complete Syntax of the UPDATE COLUMNS Command
Combining the syntax options can result in correspondingly complex commands.
UPDATE COLUMNS TABLE hotel.customer
IF POS 1 > '4000'
cno 1
SET firstname 'Secret'
SET address 5
DATA INSTREAM FILE 'customer_csv.data' CSV
For a complete syntax description, see UPDATE COLUMNS Command in the Loader documentation.