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.
Top comments (0)