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

UPDATE的问题，高手速度来指点哈
UPDATE 表A set z=b.b1 where x as b on a.id = b.id and a.type = b.type

问题：
x表 
id type
1 1
2 1
3 1

表A
z type
0 1


需要更新成
表A
z type
3 1


[解决办法]

SQL code

UPDATE 表A SET 表A.Z=X.idFROM   表A INNER  JOIN (SELECT type,MAX(id) AS id FROM X GROUP BY type) AS X       ON 表A.type=X.type
[解决办法]
UPDATE 表A SET 表A.Z=X.id
FROM   表A  
INNER  JOIN (SELECT type,MAX(id) AS id FROM X GROUP BY type) AS X
      ON 表A.type=X.type
[解决办法]
SQL code
create table tabx(id int, type int)insert into tabxselect 1, 1 union allselect 2, 1 union allselect 3, 1create table taba(z int, type int)insert into tabaselect 0, 1update a set a.z=b.maxidfrom taba ainner join(select type,max(id) maxidfrom tabx group by type) bon a.type=b.typeselect * from tabaz           type----------- -----------3           1(1 row(s) affected)
[解决办法]
SQL code
create table tabx(id int, type int, time datetime, filepath int)insert into tabx select 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', 3create table taba(z int, type int, time datetime, filepath int)insert into taba(z,type)select 0, 1update a set a.z=b.id,    a.time=b.time,    a.filepath=b.filepathfrom taba ainner join tabx bon a.type=b.typewhere b.time=(select max(time) from tabx c  where c.type=b.type)select * from tabaz           type        time                    filepath----------- ----------- ----------------------- -----------2           1           2011-01-01 17:00:00.000 2(1 row(s) affected)