Solved

Help with an SQL Query

Posted on 2009-07-14
5
234 Views
Last Modified: 2012-05-07
I have 2 tables, Codes and Targets.  They have a common key, Code.  I am trying to put together a simple query to put the targets identity into Codes as a foreign key.

This is using SQL Server 2008.

Select Code, Target from Codes as C
Select Top 1 id, Code from Targets as T where T.Code=C.Code Order By T.FromDate Desc

Those are the 2 queries that I need to join to get something along the lines of
Update Codes set Target=(SELECT TOP 1 T.ID From Targets as T WHERE T.Code=Codes.Code ORDER BY T.FromDate DESC)

How can I properly form this query?
0
Comment
Question by:GeoffSutton
[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
5 Comments
 
LVL 32

Expert Comment

by:awking00
ID: 24849815
Can you provide the relevant table structures with some sample data and the expected result?
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24849822
something like this:
UPDATE CODES
SET    Target = ID
FROM   Codes c INNER JOIN Targets t
      ON c.Code = t.Code

Open in new window

0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 24849853
update C
SET Target = t1. ID
FROM Codes c
INNER join (SELECT Code, MAX(FromDate) MaxDate from Targets GROUP BY Code ) T on t.Code = c.code
INNER JOIN Targets t1 on t.code  = t1.code and t.MaxDate = t1.FromDate
0
 
LVL 10

Author Closing Comment

by:GeoffSutton
ID: 31603268
Thanks.  I knew it was a relatively simple query, just couldn't put the pieces together.  It was quick and did exactly what I needed.
0
 
LVL 14

Expert Comment

by:shru_0409
ID: 24850074
Select Code, Target
from Codes as C, (Select Top 1 id, Code from Targets Order By T.FromDate Desc
 )  t
where T.Code=C.Code
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

728 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