Example using update columns

When only some columns in a table are specified as columns to be updated, the values corresponding to all other columns are ignored when the update statement is built and executed. For any row produced by the map, if the values for the update key columns do not match any of the existing rows in the table or view, the values of all columns will be inserted in the new row.

In the following example, a table (PersonalInfo) has the following key columns and update columns defined in the Database Interface Designer.

When this map runs, because -UPDATE is enabled for the output, the database adapter will first go through the results of the map and update all rows in the table matching the key columns in the output produced. Essentially, the following SQL statements are executed:

UPDATE PersonalInfo
 SET FirstName='Karl', LastName='March', PhoneNumber='(847) 
555-1234'
 WHERE ID = 10
UPDATE PersonalInfo
 SET FirstName='Janice', LastName='Armstrong',
 PhoneNumber='(203) 555-9898'
  WHERE ID=14

In the first UPDATE statement, because this statement does not find any rows to update, the following SQL statement is executed.

INSERT INTO PersonalInfo VALUES (10,'Karl', 'March',
'(847) 555-1234', '999-88-7766')

This execution creates a new row in the table for Karl March-including values for the ID, FirstName, LastName, PhoneNumber, and SSN columns.

In the second UPDATE statement, because an existing row has an ID value of 14, only the values of the FirstName, LastName, and PhoneNumber columns are updated because of the settings specified in the Columns to update list in the Set Table Update Key Columns dialog of the Database Interface Designer. In this example, Janice Taylor's last name changed to Armstrong and her telephone number is changed to (203) 555-9898. Her social security number remains unchanged because it is not a column that has been designated for update.