# 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](https://academy.pentaho.com/pentaho-data-integration/data-integration/data-sources/databases/cruid/database-connections).
* 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="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-70bd8a3ad0cbdef0e66fec94387d4a0cdd284eff%2FUpdate%20(1).png?alt=media" 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="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-2dd3d7ad527aba86f2226d1b618cda1192f62d8b%2FTFO%20-%20update.png?alt=media" 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="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-8c0d65cef0b13ac92e909c7a36931e3b72b765ce%2FTFI%20-%20content%20update.png?alt=media" 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="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-5a96bef5e6f55510943ee84a97d8dc9216b89247%2FTFI%20-%20fields%20update.png?alt=media" 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](https://academy.pentaho.com/pentaho-data-integration/data-integration/data-sources/databases/cruid/insert-update-db).
{% 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="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-709028060f18a23e235668d6d10faee6e7e43380%2Fupdate%20options.png?alt=media" 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="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-e7b93c44bfb173ab10ad744e18f2fd9db10197d7%2Fupdate%20-%20step%20metrics.png?alt=media" 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="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-2ecfa5bad8c0b920e99c6e19706dc22fba91260c%2Fupdate%20db.png?alt=media" 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>