[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Update Field in Table from Field in another Table

Posted on 2011-10-25
6
Medium Priority
?
428 Views
Last Modified: 2012-05-12
Hi All

I have two tables as the following

Table_A
=======
EMP_ID
LEAVE_CODE
OPENING_BAL
FIN_MONTH
FIN_YEAR
CREATED_DATE

Open in new window


Table_B
=======
EMP_ID
LEAVE_CODE
LEAVE_AVAIL
FIN_YEAR

Open in new window


But the update should be follow the conditions :-
Only the record updated should be follow the conditions
* LEAVE_CODE = 'ORDINARY'
* FIN_MONTH = 'AUG'
* FIN_YEAR = '2011'
* CREATED_DATE = '25/10/2011'

Only the record get the value should be follow the conditions
* LEAVE_CODE = 'ORDINARY'
* FIN_YEAR = '2011'

One record in Table_A should be equal one record in Table_B
0
Comment
Question by:egovernment
  • 3
  • 2
6 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 37029258
can you list your inputs as a table and show what you want the results to be, also as a table.

that is,  list sample rows/columns
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37029262
showing the expected results will let me know what you actually want to update.
0
 

Author Comment

by:egovernment
ID: 37029450
Hi sdstuber
Please check below example

Table_A
=======
EMP_ID      LEAVE_CODE        OPENING_BAL     FIN_MONTH     FIN_YEAR      CREATED_DATE 
444            ORDINARY            20                          MAY                  2011            25/10/2011
444            ORDINARY            25                          JUN                  2011              25/10/2011
444            ORDINARY            30                          JUL                  2011              25/10/2011
444            ORDINARY            0                            AUG                 2011              25/10/2011

Open in new window


Table_B
=======
EMP_ID      LEAVE_CODE        LEAVE_AVAIL      FIN_YEAR
444            ORDINARY             64                         2011
444            EMERGENCY          10                         2011
444            ORDINARY             50                         2010
444            EMERGENCY          17                         2010

 

Open in new window

                                   31/01/2011
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:sdstuber
ID: 37030179
thank you,  what is the expected result?


0
 

Author Comment

by:egovernment
ID: 37049405
Hi sdstuber

I need the result as the following: -
I need to update the Record 4 in Table_A

If you see the field OPENING_BAL in record # 4 is 0 so I need to update it with value in Field LEAVE_AVAIL from Table_B in Record 1 the value 64.

This meaning the value of field OPENING_BAL in Table_A will be 64 when
LEAVE_CODE = 'ORDINARY'
FIN_MONTH = 'AUG'
FIN_YEAR = 2011
CREATED_DATE = '25/10/2011'
in Table_A

And when you get a value from Table_B  when
LEAVE_CODE = 'ORDINARY'
FIN_YEAR = 2011

AND should be Emp_Id in Table_A = Emp_Id in Table_B
0
 
LVL 2

Accepted Solution

by:
DataCruncher earned 2000 total points
ID: 37051413
Please confirm that it is what you want here:

You want to update, for all the employees, the records in table A that were created, on October 25th 2011, for the month of August.
Also, the link between table A and B is done using the Employee ID (EMP_ID), the Leave Code (LEAVE_CODE) and Financial Year (FIN_YEAR).
If that's what you want, here is how you can do it:

UPDATE TABLE_A
   SET OPENING_BAL = (SELECT LEAVE_AVAILABLE
                      FROM TABLE_B
                      WHERE TABLE_B.EMP_ID = TABLE_A.EMP_ID
                        AND TABLE_B.LEAVE_CODE = TABLE_A.LEAVE_CODE
                        AND TABLE_B.FIN_YEAR = TABLE_A.FIN_YEAR)
WHERE FIN_MONTH = 'AUG'
  AND TRUNC(CREATED_DATE) = TO_DATE('25/10/2011','DD/MM/YYYY');
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question