updating a table involving groupby clause while avoiding temp tables

I have a transaction log table that is updated each time a record is updated.

autoid ID Date        Source
1       1 2010-10-11  abc
2       2 2010-09-10  xyz
3       1 2010-08-03  pqr
4       1 2010-11-01  mno

I can get the most recent update for each ID by the following query (it is efficient, is it?):

select * from mytable group by ID order by Date desc;

Now if I have another table that should be updated with the most recent transaction date, how do I go about doing it without creating a temp table?

The following query is incorrect, but is there a nested query alternative? I don't want to create a temp table.

update mytable a, othertable b
set b.date = a.date
where b.ID = a.ID group by ID order by Date desc;

ajreal's solution worked!

update othertable,
(select b.id, max(b.`date`) as latest from mytable b group by b.id)开发者_运维技巧 as b
set othertable.`date` = b.latest
where othertable.id=b.id
;

update mytable,
(select b.id, max(b.`date`) as latest from othertable b group by b.id) as b
set mytable.`date`=b.latest
where mytable.id=b.id
;