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:
Column name | Key | SQL type | Extended | Length | Scale | 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)