Solved

How to update a field in one table using data from a second table as a criteria?

Posted on 2008-10-07
6
230 Views
Last Modified: 2013-12-07
I have two tables, diploma and degree with columns and data as follows:

Diploma Table                                                                  Degree Table

ID     SeqNo  Order Date                                                  ID       SeqNo   Grad_Code  Term_Code
---     -------   --------------                                                  ---       --------    -------------   --------------
123   1                                                                             123     1             GR              200800
123   2                                                                             123     2             WA             200810
456   1                                                                             456     1             GR              200820

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'. How can I write an SQL statement to accomplish this? If so how? If not, can how can I do it using PL/SQL with cursors?
0
Comment
Question by:geeta_m9
  • 3
  • 2
6 Comments
 
LVL 9

Expert Comment

by:jamesgu
ID: 22664993
update Diploma
set [Order Date] = getdate()
where exists ( select 1 from [Degree Table] b
     where Diploma.ID = b.ID
     and     Diploma .SeqNo = b.SeqNo
     and     b.Grad_Code  = 'GR' and b.Term_Code = '200800'
)

0
 
LVL 9

Accepted Solution

by:
jamesgu earned 500 total points
ID: 22664996
changed to oracle syntax

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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22665007
See if this helps:
(uses a subquery to go get value -- if doesn't match will set to NULL)
Update Diploma di

Set di.[Order Date] = (SELECT SysDate FROM Degree de WHERE de.ID = di.ID AND de.SeqNo = di.SeqNo AND Grad_Code = 'GR' and Term_Code = '200800');

Open in new window

0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22665011
I like jamesqu's approach without subquery!
0
 

Author Comment

by:geeta_m9
ID: 22672056
Thank you!
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22672729
You are welcome!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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
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…

864 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

20 Experts available now in Live!

Get 1:1 Help Now