[help]一个update sql怎么写

[x0y1z2]

create table Test(
pk varchar(10) primary key,
code varchar(12),
year int,
fieldA varchar(20)
fiedlB int
);
表中有2002，2003，2004，2005的数据，每年code大多数一样的，现在要把2005的fieldA,fieldB 改为和2004的相同code对应的fieldA,fieldB

[jinjazz]

update test set fieldA=a.fieldA ,fieldB=a.fieldB
from test a where test.code=a.code and test.year=2005 and a.year=2004

[x0y1z2]

The column prefix 'test' does not match with a table name or alias name used in the query.

[x0y1z2]

The column prefix 'test' does not match with a table name or alias name used in the query.

但是test 表示有的阿

[lsxaa]

不支持别名,这样

select * into #t from 表

update test set fieldA=#t.fieldA ,fieldB=#t.fieldB
from #t where test.code=#t.code and test.year=2005 and #t.year=2004

[x0y1z2]

运行
update test set fieldA =a.fieldA ,fiedlB =a.fiedlB
from test as a where code=a.code and year=2005 and a.year=2004
and a.code='01011010'

(0 row(s) affected)
但是运行
select * from test as a ,test as b
where b.code=a.code and b.year=2005 and a.year=2004
and a.code='0101101010'

却有一条记录阿，
我什么地方写错了阿?

[lsxaa]

and a.code='0101101010'


and a.code='01011010' 这两个不一样啊

[x0y1z2]

赫赫，好像是的

ps:你上面的方法可以用的