UPDATE的有关问题，高手速度来指点哈,有关问题修正

UPDATE的问题，高手速度来指点哈,问题修正！
x表  
id type time filepath
1 1 2011-01-01 14:00:00 ....1
2 1 2011-01-01 17:00:00 ....2
3 1 2011-01-01 12:00:00 ....3

表A
z type time filepath
0 1


需要更新成
表A
z type time filepath
2 1 2011-01-01 17:00:00 ....2




UPDATE 时 以 时间最大的那个为准 更新其它列

如：UPDATE 表A set z=b.id where x as b on a.type = b.type
正常更新的是配对第一行，但X表有多行，需要*配对时间排序* 


[解决办法]

SQL code

UPDATE 表A set z=b.id from (select a.* from x as a where a.time in (select max(time)from x where type =x.type)) bwhere a.type = b.type
[解决办法]
SQL code
create table x(id int,type int,time datetime,filepath int)create table a(z int,type int,time datetime,filepath int)insert xselect 1, 1, '2011-01-01 14:00:00',1 union allselect 2, 1, '2011-01-01 17:00:00',2 union allselect 3, 1, '2011-01-01 12:00:00',3insert aselect 0,1,null,nullgoupdate a set a.z=b.id,a.time=b.time,a.filepath=b.filepath  from ajoin x b on a.type=b.typewhere b.time=(select max(time) from x where a.type=type)goselect * from agodrop table x,a