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?