Solved

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

Posted on 2008-10-13
11
157 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
11 Comments
 

Author Comment

by:geeta_m9
ID: 22704131
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
ID: 22704172
I also forgot to mention that the Hold table is joined to the Hold Type table via the hold code.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22704262
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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

Author Comment

by:geeta_m9
ID: 22704315
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 143

Expert Comment

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

?
0
 

Author Comment

by:geeta_m9
ID: 22704450
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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 22704516
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
ID: 22704575
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
ID: 22704764
It worked! You saved me a lot of trouble. Thank you so much for your help!
0
 

Author Closing Comment

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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…

696 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