I'm trying to update some rows in a table joined to another table. The left table is indexed by ID; the right table is joined on the same ID but has multiple rows per ID. My query looks like:

```
UPDATE t1
SET    t1.modified = t2.created
FROM   table1 t1
       INNER JOIN table2 t2
            ON  t1.ID = t2.ID

```

Note that t1.modified and t2.created are both datetime.
As stated, t2 has several rows per ID, each with a different created value (so the primary key is t2, created). What I want to do is set the max value of t2.created=t1.modified. However, when joining, the t1.modified value gets updated in no particular order, so whichever row is updated last, that's the value that t1.modified gets. I tried using t1.modified=max(t2.created), but apparently I can't use aggregate functions in an update query, nor can I use an order clause (i.e. order the rows so that the last row updated will effectively be the latest value of t2.created) in the update query.

Any help you can provide me is much appreciated! Thanks!!