Update DB table
Update employees in the EMPLOYEES table using the Update step.
Workshop - Update DB table
Update existing rows in EMPLOYEES using a key lookup.
You will change job titles for two employees.
What you’ll do
Read an update file with Text file input
Update matching rows with Update
Validate changes with SQL
Prerequisites
A working database connection. See Database Connections.
Basic primary key concepts (
EMPLOYEENUMBER)
Estimated time: 20 minutes
Workshop files
Create a file named employees_update.txt.
Save it in the same folder as your transformation.
This workshop assumes a comma-delimited file with no header row:
1002,Murphy,Diane,x5800,[email protected],1,1000,CEO
1102,Bondur,Gerard,x5408,[email protected],4,1056,Regional Sales Manager (EMEA)
Create a new transformation
Use any of these options to open a new transformation tab:
Select File > New > Transformation
Use
Ctrl+N(Windows/Linux) orCmd+N(macOS)
Start Spoon.
Drag Text file input onto the canvas.
Open the step properties.
Configure the file path:
File:
${Internal.Transformation.Filename.Directory}/employees_update.txt
If ${Internal.Transformation.Filename.Directory} is empty, save the transformation first.
Select Content. Use the same delimiter settings as the screenshot.
Select Get Fields.
On Fields, confirm you have these stream fields:
EMPLOYEE_NUMBERLASTNAMEFIRSTNAMEEXTENSIONEMAILOFFICECODEREPORTSTOJOBTITLE
Optional: select Preview. Confirm you get 2 rows.
Select OK.
Checkpoint: Preview shows 2 employee rows.
Update
Use Update to update existing database rows only. If a key lookup does not match, the step skips that row.
If you also need inserts, use Insert / Update DB.
Drag Update onto the canvas.
Create a hop from Text file input to Update.
Open the step properties.
Select your database Connection.
Set Target table to
EMPLOYEES.
Key lookup
Map the table key EMPLOYEENUMBER to the stream field EMPLOYEE_NUMBER.
Update fields
Select Get update fields. Then confirm mappings are correct.
Do not add EMPLOYEENUMBER as an update field.
Select OK.
Select Run in the canvas toolbar.
In Execution Results, open Step Metrics.
You should see 2 updated rows for the Update step.
Verify the updated rows:
Checkpoint: JOBTITLE matches the values from employees_update.txt.
Troubleshooting
Step updates 0 rows
Confirm EMPLOYEENUMBER exists in EMPLOYEES. The Update step does not insert.
Updates fail with data type errors
Make sure EMPLOYEE_NUMBER is numeric. Use Select values to cast if needed.
Wrong rows updated
Confirm the key mapping is EMPLOYEENUMBER (table) = EMPLOYEE_NUMBER (stream).
Last updated
Was this helpful?