Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

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

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
geeta_m9
Asked:
geeta_m9
  • 3
  • 2
1 Solution
 
jamesguCommented:
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
 
jamesguCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
Kevin CrossChief Technology OfficerCommented:
I like jamesqu's approach without subquery!
0
 
geeta_m9Author Commented:
Thank you!
0
 
Kevin CrossChief Technology OfficerCommented:
You are welcome!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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