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