Solved

How to update a field in one table using data from three others tables as a criteria?

Posted on 2008-10-13
11
154 Views
Last Modified: 2012-05-05
I have two tables, diploma and degree with columns and data as follows:

Diploma Table                                         Degree Table                                                    Hold Table                              Hold Type Table

ID     SeqNo  Order Date                         ID       SeqNo   Grad_Code  Term_Code           ID    Hold_Code Hold_Date    Hold_Code Hold_Type
---     -------   --------------                         ---       --------    -------------   --------------           --     ------------- ------------       ------------   ------------
123   1                                                    123     1             GR              200800                123  Null                                 Null            Null  
123   2                                                    123     2             WA             200810                456  AB              31-DEC-08    AB              AR            
456   1                                                    456     1             GR              200800                789  AB              31-DEC-08    AB              AS
789   1                                                    789     1             GR              200800        

I would like to update the Order Date in the Diploma table with the system date based on the matching ID value and seq number in the Degree table with Grad_Code  = 'GR' and Term_Code = '200800'. Also, if the person has an 'AR' value as the Hold Type in the Hold Type Table and the hold date in the Hold Table is greater than the current date  then the Order Date should not be updated. How can I write an SQL statement to accomplish this? For instance person with ID 123 should have the Order Date in the Diploma table for SeqNo 1 since the term_code is 200800 and grad_code= 'GR' for that record and he/she does not have a hold type of AR in the Hold Code table. Similarly, ID 789 should also have the Order Date updated because the term_code = '200800', Grad_Code='GR' and also because the hold type in the Hold Type table is not 'AR. However, ID 456 should not have the Order Date updated because of the 'AR' in the hold_code type and the hold_date is greater than the current date.
0
Comment
Question by:geeta_m9
  • 7
  • 3
11 Comments
 

Author Comment

by:geeta_m9
Comment Utility
There was a solution given for a similar question which I posted earlier (see below). However, for that question, I only used two tables. Now it is more complicated.

update Diploma
set  Order_Date = sysdate
where exists ( select 1 from  Degree  b
     where Diploma.ID = b.ID
     and     Diploma .SeqNo = b.SeqNo
     and     b.Grad_Code  = 'GR' and b.Term_Code = '200800'
)
0
 

Author Comment

by:geeta_m9
Comment Utility
I also forgot to mention that the Hold table is joined to the Hold Type table via the hold code.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
you mean:

 

update Diploma

set  Order_Date = sysdate

where exists ( select 1 from  Degree  b

     where Diploma.ID = b.ID

     and     Diploma .SeqNo = b.SeqNo

     and     b.Grad_Code  = 'GR' and b.Term_Code = '200800'

     and not exists ( select null from Hold h

                   where h.ID = b.ID

                     and h.Hold_Code = 'AR'

                     and h.hold_Date > SYSDATE

                    )

         )

Open in new window

0
 

Author Comment

by:geeta_m9
Comment Utility
The hold code value of 'AR' is found in the Hold Type table and not in the Hold table. However, I don't see that table being used in the solution.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
> and not exists ( select null from Hold h

?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:geeta_m9
Comment Utility
I your query you had a subquery:

select null from Hold h
                   where h.ID = b.ID
                     and h.Hold_Code = 'AR'
                     and h.hold_Date > SYSDATE

However, the problem is that the value 'AR' comes from the Hold Type table and not from the Hold table (if you look at the table outline I gave in my question). So do I have to do another subquery involving the Hold table with the Hold Type table, if so how?
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
sorrry
update Diploma

set  Order_Date = sysdate

where exists ( select 1 from  Degree  b

     where Diploma.ID = b.ID

     and     Diploma .SeqNo = b.SeqNo

     and     b.Grad_Code  = 'GR' and b.Term_Code = '200800'

     and not exists ( select null from Hold h

                     join HoldType ht

                       on ht.hold_Code = h.hold_code

                       and ht.code_type = 'AR'

                   where h.ID = b.ID

                     and h.hold_Date > SYSDATE

                    )

         )

Open in new window

0
 

Author Comment

by:geeta_m9
Comment Utility
That looks good. Let me test it out now and will get back to you in a few minutes.
0
 

Author Comment

by:geeta_m9
Comment Utility
It worked! You saved me a lot of trouble. Thank you so much for your help!
0
 

Author Closing Comment

by:geeta_m9
Comment Utility
You're brilliant! Thank you for an accurate and speedy solution.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now