Re: Converting from Ingres Reports to Oracle

DI Karl Heinz Hörmann (kh.hoermann_at_penta.at) wrote:

: Malcolm Dew-Jones wrote in <3885f951_at_news.victoria.tc.ca>...
: >--
: >

: how would you update a join ??

Very easily in some non-Oracle databases. Every once in a while I try it out in to see if Oracle supports it yet, as it is much easier to write than the "standard" update.

: > (oracle version?)
: > update load108_temp l , vehicles v
: > set l.vehicle_sname = v.short_name
: > where l.vehicle = v.key_number;

: what should that mean ??

Join the tables on a key, and copy the values in one column to another column. As long as the targetted column is uniquely identified in its underlying table then there is no reason the data shouldn't be updateable. Its not really any different than allowing a user to update a view.

: the correct syntax is:

: update load108_temp
: set vehicle_sname = (select short_name from vehicles
: where key_number = vehicle)

: the aliasing is only useful and needed, if there are columns with the same
: name in both tables

Aliassing is allowed whether it's is needed or not, so your syntax is no more "correct". Aliassing is useful anytime I want to make explicit which table is involved without having to write a full table name. In fact in the above situation I don't beleive you ever saw the column names in the table, so the second part of your own comment may be relevent. Even when column names are unique it can be useful to explicitly show the table containing the column, in no small part to have the SQL compiler check that your understanding of the data is correct.

: >update load108_temp l
: >set vehicle_sname =
: >(select short_name from vehicles v
: >where l.vehicle = v.key_number)
: >WHERE exists (select * from vehicles
: > where l.vehicle = v.key_number)
: >;

: the WHERE clause is useless -

As I said it might be.

Not having had to perform such an update within Oracle in the last month or so I took the conservative approach to ensure the correct data was targetted. Please excuuuuse me if I have to work on a variety of systems, each of which has its own minor variations and limitations.

: sorry ...

You are forgiven.

-- Received on Tue Jan 25 2000 - 13:15:22 CST