Re: Help Help Help Help Help Help Help Help Help Help Help Paleeeeeezzzzzzz!!!!!!!!

Rmplstlskn wrote:
:
: Use a trigger and you will not need to run a batch job
: and your denormalized data [bad, bad] will always be current
:
: In your trigger body on the driving table [table1] put the following DML
: statment
: Ok, to save you trouble I will just write the trigger for you
:
: create or replace trigger equipment_bur
: before update on equipment
: referencing new as new old as old
: for each row
: begin
: update pm
: set pm.pm1 = :NEW.meterreading
: where pm.eqnum = :NEW.eqnum;
: end;
: /
:
: --
: RMPLSTLSKN_at_iei.com
 

: RogerRabbit_at_TuneTownUSA.com wrote in article
: : I have a problem that I need to solve at work, and thinking that
: : there is a good chance that someone in this conference/news group
: : would know how to tackle it, I decided to give this avenue a try...
: :
: : Here it is;
: : 1. I need to update a table with information from another table.
: : 2. These two tables are 'linked' by their equipment number (eqnum).
: : 3. Table names are PM and EQUIPMENT.
: : 4. The two rows (fields) are of the same type/size and match.
: : 5. The row to update is from the PM table and is called PM1
: : (NUM 15.2 NOT NULL)
: : 6. The row the information is from the EQUIPMENT table and is
: : called METERREADING (NUM 15.2 NOT NULL)
: :
: : The way I was thinking about doing this would be using the logic
: : listed below. If someone could help me in the syntax and correct
: : pl/sql-or just sql statments... I would be in your dept... (and
: : yes I do payoff!!!)
: :
: : open the EQUIPMENT table
: : open the PM table
: : get record 1 from EQUIPMENT
: : get record 1 from PM
: : loop
: : loop
: : if pm.eqnum=equipment.eqnum then
: : update pm.pm1 with equipment.meterreading
: : goto top of pm table
: : exit loop
: : else
: : if not eof then incr PM table by one
: : end if
: : if eof then exit loop
: : end loop
: : if not eof then incr EQUIPMENT table by one
: : end loop
: : This might have holes in it (and probably does),
: : so feel free to suggest away.
: :
: : Other usefull information that might be needed...
: : * These tables are insalled on a Oracle v7.1 rdbms.
: : * This has to 'batch run' nightly.
: :
: : If there are any other questions that you need to ask
: : (if there is someone nice enough to help) you can
: : contact me at motofox_at_primenet.com)
: : Thanx alot in advance...
: :

I agree, the trigger is the best solution, but if for any reason you are not able to use it, try this:

UPDATE PM TARGET
 SET PM1 = ( SELECT meterreading FROM equipment SOURCE1 WHERE TARGET.eqnum = SOURCE1.eqnum ) WHERE eqnum IN ( SELECT EQNUM FROM equipment );

Hope it helps

Alik Shapiro
Oracle DBA, EDS
Phone (317)240-5558
E-MAIL : fz1dmj_at_agt.gmeds.com

The above statements and opinions are my own and do not necessarily represent those of EDS. Received on Mon Dec 09 1996 - 00:00:00 CET