Update data in table based on data in another table

 table1
-----------------------------
| id (int) |  dt (datetime) |
-----------------------------
|    1     |  12-12-2012    |
|    2     |  13-11-2013    |
|    3     |  23-07-2014    |
|    4     |  13-06-2014    |
-----------------------------

 table2
-----------------------------
| id (int) | dt2 (datetime) |
-----------------------------
|    1     |  12-12-2012    |
|    1     |  13-11-2013    | -> update table1 id=1 with this dt2
|    2     |  23-07-2014    |
|    2     |  13-06-2014    |
|    2     |  12-12-2012    | -> update table1 id=2 with this dt2
|    3     |  13-11-2013    | -> update table1 id=3 with this dt2
|    3     |  23-07-2014    |
|    3     |  13-06-2014    |
|    4     |  23-07-2014    |
|    4     |  13-02-2014    | -> update table1 id=4 with this dt2
-----------------------------

I want to update table1.dt with the corresponding dt2 from table2 based on the id.

However, I do not want to update table1.dt to the largest corresponding datetime value from table2.dt2.

I only want to update dt to the largest corresponding dt2 which is not greater than the current date.

So far what I have gotten is:

update table1
set table1.dt = table2.dt2
from table2
inner join table1  on table1.id = table2.id
where ?table1.id=table2.id and...?

No idea how to modify the sql statement such that it will only update dt to the largest corresponding dt2 which is not greater than the current date.

Hope it isn't too confusing...

---

You could either using group and aggregates by in your derived table there, or possibly look into cross apply. Here's an example of the former.

update table1
set table1.dt = table2.dt2
from table1 join (
       select id,max(dt2) as maxDT
       from table2
       group by id) as derivedTable2 on derivedTable2 .id = table1.id