Solved

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

Posted on 2008-10-07
6
260 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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

Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

Question has a verified solution.

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

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…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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.
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
Suggested Courses

615 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