Help with an SQL Query

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?
LVL 10
Who is Participating?
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
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
Can you provide the relevant table structures with some sample data and the expected result?
something like this:
SET    Target = ID
FROM   Codes c INNER JOIN Targets t
      ON c.Code = t.Code

Open in new window

GeoffSuttonAuthor Commented:
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.
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.