Data Transformation Engine Database Interface Designer Reference Guide

Chapter 6 - Updating Database Tables Example Using Update Columns
Database Interface Designer Reference Guide
109
The contents of the table after successful map execution will be:
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='[phone]'
WHERE ID = 10
UPDATE PersonalInfo
SET FirstName='Janice', LastName='Armstrong',
PhoneNumber='[phone]'
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',
'[phone]', '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 box of the Database Interface Designer.
In this example, Janice Taylor’s last name changed to Armstrong and her
telephone number is changed to [phone]. Her social security number
remains unchanged because it is not a column that has been designated for
update.