Re: Converting from Ingres Reports to Oracle

Malcolm Dew-Jones wrote in <3885f951_at_news.victoria.tc.ca>...
>--
>
>Technical (technical_at_fma-systems.com) wrote:
>(...snip he wanted help with the following )
>
>: update load108_temp l
>: set vehicle_sname = vehicles.short_name
>: where l.vehicle = vehicles.key_number;
>
>: update load108_temp l
>: set driver_sname = personnel.short_name
>: where l.driver = personnel.key_number;
>
>
>The update command only references a single table called LOAD108_TEMP.
>That table does not have columns named vehicles.xxx or personnel.xxx.
>
>In MS-Access you could join the tables to do the update. I don't know if
>that can be done in oracle, but it's the easiest to write...

since you don't know a) SQL and b) ORACLE you should refrain from posting Technical is completely right ...

> update load108_temp l inner join vehicles v
> on l.vehicle = v.key_number
> set l.vehicle_sname = v.short_name ;

how would you update a join ??

> (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 ??

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

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

sorry ... Received on Mon Jan 24 2000 - 22:21:53 CST