UPDATE COLUMNS 

You can use the UPDATE COLUMNS command to change individual column values of a table.

Prerequisites

You require the Demo Database DEMODB.

The table specified must be available in the database. Start the Loader as database administrator MONA and log on to the demo database DEMODB. Use the Session Mode.

loadercli –d demodb –u mona,red

Examples

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 changed in the CSV format (default value).

The CNO column is the qualification column. In the CUSTOMER table, the 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 changed in the 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 changed in the 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, the rows for updating column values 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 only these records in the specified table. Observe 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 LONG Values

The data of the HOTEL table in the HOTEL schema is to be updated. The HOTEL table has the LONG column INFO. Enter the LOB syntax elements 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.long'

The NAME and INFO columns in the HOTEL table are updated. The LONG values are stored in the data stream hotel_info_csv.long.

See also:

Loader, Exporting and Importing LONG 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.

See also:

Commands for Exporting

Commands for Importing