# Update DB table

{% hint style="warning" %}

#### 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](/pentaho-data-integration/data-integration/data-sources/databases/cruid/database-connections.md).
* Basic primary key concepts (`EMPLOYEENUMBER`)

**Estimated time:** 20 minutes
{% endhint %}

***

{% hint style="info" %}
**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)
```

{% endhint %}

***

<figure><img src="/files/luKDRk7sJP1sC6OUE0jF" alt="" width="563"><figcaption><p>Update employees</p></figcaption></figure>

{% hint style="info" %}
**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)
  {% endhint %}

***

{% tabs %}
{% tab title="1. Text file input" %}
{% hint style="info" %}

#### Text file input

Read the incoming updates from `employees_update.txt`.
{% endhint %}

1. Start Spoon.

{% hint style="info" %}
{% tabs %}
{% tab title="Windows (PowerShell)" %}

```powershell
Set-Location C:\Pentaho\design-tools\data-integration
.\spoon.bat
```

{% endtab %}

{% tab title="macOS / Linux" %}

```bash
cd ~/Pentaho/design-tools/data-integration
./spoon.sh
```

{% endtab %}
{% endtabs %}
{% endhint %}

2. Drag **Text file input** onto the canvas.
3. Open the step properties.
4. Configure the file path:
   * **File:** `${Internal.Transformation.Filename.Directory}/employees_update.txt`

<figure><img src="/files/XAJFHvePsYw5iO6oZRqf" alt=""><figcaption><p>Set file path</p></figcaption></figure>

{% hint style="info" %}
If `${Internal.Transformation.Filename.Directory}` is empty, save the transformation first.
{% endhint %}

5. Select **Content**. Use the same delimiter settings as the screenshot.

<figure><img src="/files/yAxiIQhIuZwGTBxIY9yV" alt=""><figcaption><p>Text file input - Content</p></figcaption></figure>

6. Select **Get Fields**.
7. On **Fields**, confirm you have these stream fields:
   * `EMPLOYEE_NUMBER`
   * `LASTNAME`
   * `FIRSTNAME`
   * `EXTENSION`
   * `EMAIL`
   * `OFFICECODE`
   * `REPORTSTO`
   * `JOBTITLE`

<figure><img src="/files/Z3nai2uadnbxLoYj0Tni" alt=""><figcaption><p>Text file input - Fields</p></figcaption></figure>

8. Optional: select **Preview**. Confirm you get 2 rows.
9. Select **OK**.

{% hint style="success" %}
Checkpoint: Preview shows 2 employee rows.
{% endhint %}
{% endtab %}

{% tab title="2. Update" %}
{% hint style="info" %}

#### Update

Use **Update** to update existing database rows only.\
If a key lookup does not match, the step skips that row.
{% endhint %}

{% hint style="info" %}
If you also need inserts, use [Insert / Update DB](/pentaho-data-integration/data-integration/data-sources/databases/cruid/insert-update-db.md).
{% endhint %}

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

<figure><img src="/files/LvzhLxcweAoUdlO1ofb8" alt="" width="563"><figcaption><p>Update fields</p></figcaption></figure>

{% hint style="info" %}
**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.
{% endhint %}

6. Select **OK**.
   {% endtab %}

{% tab title="3. Run and validate" %}
{% hint style="info" %}

#### Run and validate

Run the transformation. Then validate the changes in the database.
{% endhint %}

1. Select **Run** in the canvas toolbar.
2. In **Execution Results**, open **Step Metrics**.

<figure><img src="/files/IFb6orkG7JKxnaAC0SoA" alt=""><figcaption><p>Step metrics</p></figcaption></figure>

{% hint style="info" %}
You should see **2 updated** rows for the Update step.
{% endhint %}

3. Verify the updated rows:

```sql
select * from EMPLOYEES
where EMPLOYEENUMBER in ('1002','1102');
```

<figure><img src="/files/GJRaZMM1lw5q5mhFX8Wa" alt=""><figcaption><p>Update employees</p></figcaption></figure>

{% hint style="success" %}
Checkpoint: `JOBTITLE` matches the values from `employees_update.txt`.
{% endhint %}
{% endtab %}
{% endtabs %}

<details>

<summary>Troubleshooting</summary>

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

</details>


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://academy.pentaho.com/pentaho-data-integration/data-integration/data-sources/databases/cruid/update-db-table.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.