Solved

Help with an SQL Query

Posted on 2009-07-14
5
229 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

733 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