Updating an Oracle table

This example shows an Oracle table being updated with three new columns. The database records the horse health records of a large stud. Details of the worming records are being added to the main table and populated with the most recent data, using the existing column "name" as a key. The metadata for the new columns is as follows:

Table 1. Column metadata on the Properties tab
Column name	Key	SQL type	Extended	Nullable
name	Yes	Char		No
wormer_type		Char	Unicode	No
dose_interval		Char	Unicode	No
dose_level		Char	Unicode	No

Specify upsert as the write method and select User-defined Update & Insert as the upsert mode. The existing name column is not included in the INSERT statement. The properties (showing the INSERT statement) are shown below. The INSERT statement is as generated by the IBM® InfoSphere™ DataStage®, except the name column is removed.

INSERT
INTO
horse_health
(wormer_type, dose_interval, dose_level)
VALUES
(ORCHESTRATE.name,
ORCHESTRATE.wormer_type,
ORCHESTRATE.dose_interval,
ORCHESTRATE.dose_level)

The UPDATE statement is as automatically generated by the InfoSphere DataStage:

UPDATE
horse_health
SET
wormer_type=ORCHESTRATE.wormer_type,
dose_interval=ORCHESTRATE.dose_interval,
dose_level=ORCHESTRATE.dose_level
WHERE
(name=ORCHESTRATE.name)