loading...

Updating a Table with the Values of a Different One

pesse profile image Samuel Nitsche Originally published at cleandatabase.wordpress.com on ・2 min read

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.

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: ()

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

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:

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:

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

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:

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 and on my GitHub repository.

Posted on Jun 7 '19 by:

pesse profile

Samuel Nitsche

@pesse

Curiosity-driven software-developer, database geek, always willing to learn. Compassionate coding advocate, father, husband, 10x underpants. We don't need more rockstars, we need more mentors.

Discussion

markdown guide