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 IDPlanet NamePlanet Faction
3Dromund Kaasimperium

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: <PlanetName> (<Garrison ID>)

Continue reading

Pitfalls of the MINUS-comparison

My work includes a lot of database views because they work as a kind of public API in our case. Since I started talking about self-testing and utPLSQL, the MINUS-comparison has always had its place when assuring that a new, reconstructed view has the exact same content as the old one.

However, I did it wrong until recently when I discovered a major pitfall.

For I like to tell stories let’s start with a Star Wars scenario again: We have a table which contains all the Star Wars characters and another table which holds information in which of the “Episode”-movies they appeared.

Continue reading