Background documentationUPDATE COLUMNS Locate this document in the navigation structure

 

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.

More Information

Commands for Exporting

Commands for Importing

Session Mode