Update via a VIEW or Direct Update

[AlStl]

NEWBIE to ORACLE SQL and I have a following situation:

Table X_1 (PRIMARY KEY A_1)
A_1,A_2,A_3,A_4
8998A,01/01/2014,XYXY,REST WELL
BHYXTY,04/20/2015,UUUU,PEACE
99999,02/06/2014,LLLL,FUN

TABLE X_2 (F_1 & F_2 PRIMARY KEY)
F_1,F_2,F_3,F_4,A_2,A_3,A_4
8998A,0,TEST,XXX,NULL,XYXY,NULL
BHYXTY,1,TEST,YYY,NULL,NULL,NULL
99999,0,TEST,ZZZ,09/09/2015,NULL,NULL
8998A,2,TEST,XXX,NULL,NULL,NULL
BHYXTY,4,TEST,YYY,01/01/2004,NULL,NULL

I ONLY want to UPDATE X_2.A_2, X_2.A_3, X_2.A_4 with X_1.A_2, X_1.A_3, X_1.A_4 Values where X_1.A_1=X_2.F_1 AND X_2.F_2 is MAX or highest.

Shall I create a view with MAX X_2.F_2 and then use the view and table X_1 to deal with situation or Is there any clever way to write an update statement using just X_1 and X_2 that will do the job?

Any help or suggestion will be highly appreciated.

Al

 

[Andrzejek]

Is this how your data looks like?

[pre]
TABLE X_2 (F_1 & F_2 PRIMARY KEY)
F_1 F_2 F_3 F_4 A_2 A_3 A_4
8998A 0 TEST XXX NULL XYXY NULL
BHYXTY 1 TEST YYY NULL NULL NULL
99999 0 TEST ZZZ 09/09/2015 NULL NULL
8998A 2 TEST XXX NULL NULL NULL
BHYXTY 4 TEST YYY 01/01/2004 NULL NULL
[/pre]

If so, select the text and use PRE tag.
Use Preview before posting.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[AlStl]

Hey Andy, Yes it does. Thx for the suggestion...Here is the fixed version for both tables:



[pre]Table X_1 (PRIMARY KEY A_1)[/pre]

[pre]A_1 A_2 A_3 A_4 [/pre]
[pre]8998A 01/01/2014 XYXY REST WELL [/pre]
[pre]BHYXTY 04/20/2015 UUUU PEACE [/pre]
[pre]99999 02/06/2014 LLLL FUN [/pre]

[pre]TABLE X_2 (F_1 & F_2 PRIMARY KEY)[/pre]

[pre]F_1 F_2 F_3 F_4 A_2 A_3 A_4[/pre]
[pre]8998A 0 TEST XXX NULL XYXY NULL[/pre]
[pre]BHYXTY 1 TEST YYY NULL NULL NULL[/pre]
[pre]99999 0 TEST ZZZ 09/09/2015 NULL NULL[/pre]
[pre]8998A 2 TEST XXX NULL NULL NULL[/pre]
[pre]BHYXTY 4 TEST YYY 01/01/2004 NULL NULL[/pre]

 

[Beilstwh]

Yes, you can update through a view two different ways.

1) if the view doesn't use aggregates such as max, min, in other words simple joins then you can update directly through the view. However you can update any table in the simple select but only one table at a time.

2) By using INSTEAD of triggers on the view you can do anything you want to with the under laying data or any other table you want to.

Bill
Lead Application Developer
New York State, USA