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?
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?
awking00
Can you provide the relevant table structures with some sample data and the expected result?
something like this:
UPDATE CODES
SET Target = ID
FROM Codes c INNER JOIN Targets t
ON c.Code = t.Code
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
from Codes as C, (Select Top 1 id, Code from Targets Order By T.FromDate Desc
) t
where T.Code=C.Code