• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 162
  • Last Modified:

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

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
geeta_m9
Asked:
geeta_m9
  • 7
  • 3
1 Solution
 
geeta_m9Author Commented:
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
 
geeta_m9Author Commented:
I also forgot to mention that the Hold table is joined to the Hold Type table via the hold code.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
geeta_m9Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
> and not exists ( select null from Hold h

?
0
 
geeta_m9Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
geeta_m9Author Commented:
That looks good. Let me test it out now and will get back to you in a few minutes.
0
 
geeta_m9Author Commented:
It worked! You saved me a lot of trouble. Thank you so much for your help!
0
 
geeta_m9Author Commented:
You're brilliant! Thank you for an accurate and speedy solution.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now