SQL update to record with nearest data

In SQL server 2008:

Suppose I have two tables.

Table1 has 3 fields: Name, Date1 and Date2. Currently, all the Date2 entries are NULL. (Name, Date1) form a unique key.

Table2 has 2 fields: Name and Date2. (Name, Date2) form a unique key.

Every "Name" in Table1 has at least one corresponding entry in Table2.

Now, I want to update all the Date2 entries in Table1 (remember they are all NULL right now) to the Date2 entry in Table2 that is the closest to Date1 in Table1. I.e. the date that would give the result of:

 min(datediff(dd,Table1.Date1,Table2.Date2))

So to be clear, if I have the following entries:

Table1:

[Name]: Karl, [Date1]: 1/1/2009, [Date2]: NULL

Table2:

[Name]: Karl, [Date2]: 1/1/2000

[Name]: Karl, [Date2]: 1/7/2009

[Name]: Karl, [Date2]: 1/1/2010

Then I want to update Table1.Date2 to '1/7/2009' since that is the closest date to '1/1/2009'.

T开发者_如何学Pythonhanks a lot

Karl

---

WITH abcd AS
 (
 SELECT t1.Name,t1.Date1, t2.Date2
  ,RANK() OVER (ORDER BY ABS(DATEDIFF(dd, t1.Date1, t2.Date2)) ASC) AS rnk
 FROM 
  Table1 AS t1 
  JOIN Table2 AS t2 ON t1.Name = t2.Name
 )
UPDATE Table1 SET
    [Date2] = (SELECT TOP(1) [Date2] FROM abcd WHERE rnk = 1)

---

UPDATE  Table1
SET     Date2 = t2.Date2
FROM    Table1 t1
JOIN    Table2 t2
    ON  t1.Name = t2.Name
    AND ABS(DATEDIFF(d, t1.Date1, t2.Date2)) = (SELECT  MIN(ABS(DATEDIFF(d, t1.Date1, t2.Date2)))
                                                FROM    Table1 t1
                                                JOIN    Table2 t2
                                                    ON  t1.Name = t2.Name
                                                )

Check if you need ABS - I guess you do.

Also the query does not handle the case where there are 2 dates in Table2 with the same distance to the Date1, but from different sides.