This scenario is based on the data aggregation scenario, Scenario 1: Aggregating table columns and filtering. As the data update action is available in Oracle DB, this scenario describes a Job that updates particular data in the agg_result table.
As described in Scenario 1: Aggregating table columns and filtering, set up a Job for data aggregation using the corresponding ELT components for Oracle DB, tELTOracleInput, tELTOracleMap, and tELTOracleOutput, and execute the Job to save the aggregation result in a database table named agg_result.
When defining filters in the ELT Map editor, note that strings are case sensitive in Oracle DB.
Launch the ELT Map editor and add a new output table named update_data.
Add a filter row to the update_data table to set up a relationship between input and output tables:
owners.ID_OWNER = agg_result.ID_OWNER.
Drop the MAKE column from the cars table to the update_data table.
Drop the NAME_RESELLER column from the resellers table to the update_data table.
Add a model enclosed in single quotation marks, A8 in this use case, to the MAKE column from the cars table, preceded by a double pipe.
Add Sold by enclosed in single quotation marks in front of the NAME_RESELLER column from the resellers table, with a double pipe in between.
Check the Generated SQL select query tab to be executed.
Click OK to validate the changes in the ELT Mapper.
Deactivate the tELTOracleOutput component labeled Agg_Result by right-clicking it and selecting Deactivate Agg_Result from the contextual menu.
Drop a new tELTOracleOutput component from the Palette to the design workspace, and label it Update_Data to better identify its functionality.
Connect the tELTOracleMap component to the new tELTOracleOutput component using the link corresponding to the new output table defined in the ELT Mapper, update_data in this use case.
Double-click the new tELTOracleOutput component to display its Basic settings view.
From the Action on data list, select Update.
Check the schema, and click Sync columns to retrieve the schema structure from the preceding component if necessary.
In the WHERE clauses area, add a clause that reads
agg_result.MAKE = 'Audi'to update data relating to the make of Audi in the database table agg_result.
Fill the Default Table Name field with the name of the output link, update_data in this use case.
Select the Use different table name check box, and fill the Table name field with the name of the database table to be updated, agg_result in this use case.
Leave the other parameters as they are.
Save your Job and press F6 to run it.
The relevant data in the database table is updated as defined.