Updating a Table with the Values of a Different One
Tags: 100codeexamples, oracle, sql, database
Published: 2019-06-07 07:18:19

---
title: Updating a Table with the Values of a Different One
published: true
tags: #100CodeExamples,Oracle,sql,database
canonical_url: https://cleandatabase.wordpress.com/2019/04/17/updating-a-table-with-the-values-of-a-different-one/
---

I am working professionally with databases for over 15 years now and have a huge focus on Oracle – but I really keep forgetting how to update a table with values of a different one (this is one thing which is so much easier in SQL Server by the way).

Therefore let’s assume we have a table containing planets and one containing garrisons which are on these planets.

| **Garrison ID** | **Planet Name** | **Planet Faction** |
|---|---|---|
| 1 | Korriban | imperium |
| 2 | Korriban | imperium |
| 3 | Dromund Kaas | imperium |
| 4 | Hoth | republic |

We would now like to have a new column in the garrisons table which can contain a name.

```sql
alter table garrisons add name varchar2(300)
```

The imperial side now has a request to update all their garrisons with a name according to this schema: <PlanetName> (<Garrison ID>)

The base select query to get the information needed would be like this:

```sql
select
  garrisons.id,
  planets.name
  from
    garrisons
    inner join planets
      on garrisons.fk_planet = planets.id
  where
    planets.faction = 'imperium'
```

## Possibility #1: PL/SQL FOR-Loop

Yes, this is ugly and slow, but it shows what we essentially want to do:

```sql
begin
  for rec in (select
    garrisons.id,
    planets.name
    from
      garrisons
      inner join planets
        on garrisons.fk_planet = planets.id
    where
      planets.faction = 'imperium'
  )
  loop
    update garrisons
      set name = rec.name || ' (' || to_char(rec.id) || ')'
      where id = rec.id;
  end loop;
end;
```

## Possibility #2: Correlated Update

This is the most suggested way if you search for “oracle update table from results of another table”, but I find it pretty hard to read and can never remember how to do it properly:

```sql
update garrisons
  set name = (
    select
        planets.name || ' (' || to_char(garrisons.id) || ')'
      from planets
      where planets.id = garrisons.fk_planet
    )
where exists ( -- Limitation to Imperium
  select 1 from planets
    where planets.id = garrisons.fk_planet
      and planets.faction = 'imperium'
  );
```

## Possibility #3: MERGE

Yes, this looks pretty nice because we can keep the initial query and I find it to be very expressive. However, you would probably assume a `MERGE` statement to handle `INSERT`, too, and you can’t update the columns which are used in the `ON` clause (which is possible in Possiblity #2):

```sql
merge into garrisons target
  using (
    select
      garrisons.id,
      planets.name
      from
        garrisons
        inner join planets
          on garrisons.fk_planet = planets.id
      where
        planets.faction = 'imperium'
  ) source
  on (target.id = source.id)
  when matched then
    update set
      target.name = source.name || ' (' || to_char(source.id) || ')';
```

## Possibility #4: Inline-View Update

This looks pretty neat, but is restricted to [key-preserved tables](https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:548422757486):

```sql
update (
  select
    garrisons.id,
    garrisons.name garrison_name,
    planets.name planet_name
    from
      garrisons
      inner join planets
        on garrisons.fk_planet = planets.id
    where
      planets.faction = 'imperium'
)
  set garrison_name = planet_name || ' (' || to_char(id) || ')';
```

As always, you can find the full example on [LiveSQL](https://livesql.oracle.com/apex/livesql/s/h9fxpvfssti0lql9cr85kw4p6) and on my [GitHub repository](https://github.com/pesse/sith-demo-db/blob/master/100CodeExamples/14_update_table_from_another_table.sql).