Re: Converting from Ingres Reports to Oracle

--

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

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

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

In Oracle you can certainly use a sub-query, though my SQL may not be quite correct, and I do not know if my main WHERE clause is required here or not...

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

-- Received on Wed Jan 19 2000 - 11:50:09 CST