INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Update via a VIEW or Direct Update

Update via a VIEW or Direct Update

(OP)
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

RE: Update via a VIEW or Direct Update

Is this how your data looks like?

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
 

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.

RE: Update via a VIEW or Direct Update

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



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 

RE: Update via a VIEW or Direct Update

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close