Solved

Help with an SQL Query

Posted on 2009-07-14
5
196 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
5 Comments
 
LVL 31

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

759 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now