Ctrlk
Start your 30-day Pentaho Enterprise Evaluation ..
For the complete documentation index, see llms.txt. This page is also available as Markdown.

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,dmurphy@classicmodelcars.com,1,1000,CEO
1102,Bondur,Gerard,x5408,athompson@classicmodelcars.com,4,1056,Regional Sales Manager (EMEA)
Update employees

Create a new transformation

Use any of these options to open a new transformation tab:

  • Select File > New > Transformation

  • Use Ctrl+N (Windows/Linux) or Cmd+N (macOS)

Text file input

Read the incoming updates from employees_update.txt.

  1. Start Spoon.

  1. Drag Text file input onto the canvas.

  2. Open the step properties.

  3. Configure the file path:

    • File: ${Internal.Transformation.Filename.Directory}/employees_update.txt

Set file path

If ${Internal.Transformation.Filename.Directory} is empty, save the transformation first.

  1. Select Content. Use the same delimiter settings as the screenshot.

Text file input - Content

  1. Select Get Fields.

  2. On Fields, confirm you have these stream fields:

    • EMPLOYEE_NUMBER

    • LASTNAME

    • FIRSTNAME

    • EXTENSION

    • EMAIL

    • OFFICECODE

    • REPORTSTO

    • JOBTITLE

Text file input - Fields

  1. Optional: select Preview. Confirm you get 2 rows.

  2. 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.

  1. Drag Update onto the canvas.

  2. Create a hop from Text file input to Update.

  3. Open the step properties.

  4. Select your database Connection.

  5. Set Target table to EMPLOYEES.

Update fields

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.

  1. Select OK.

Run and validate

Run the transformation. Then validate the changes in the database.

  1. Select Run in the canvas toolbar.

  2. In Execution Results, open Step Metrics.

Step metrics

You should see 2 updated rows for the Update step.

  1. Verify the updated rows:

Update employees

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).

PreviousRead DB tableNextInsert / Update DB

Last updated

Was this helpful?