Solved

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

Posted on 2008-10-07
6
252 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
[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
  • 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 60

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
Technology Partners: 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 60

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 60

Expert Comment

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

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

738 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