怎样交换两条记录中某个字段的值，最好只用一条语句！该怎么解决

怎样交换两条记录中某个字段的值，最好只用一条语句！
原表如下
id ziduan1 ziduan2  
... ...
1 a1 a2
2 b1 b2
... ...


数据交换结果

id ziduan1 ziduan2  
... ...
1 a1 b2
2 b1 a2
... ...


已知id，只交换ziduan2的值，最好只用一条语句，求快速的办法

[解决办法]

SQL code

declare @T table (id int,ziduan1 varchar(2),ziduan2 varchar(2))insert into @Tselect 1,'a1','a2' union allselect 2,'b1','b2'update @Tset ziduan2=case when ziduan1='a1' then (select ziduan2 from @T where ziduan1='b1')when ziduan1='b1' then (select ziduan2 from @T where ziduan1='a1')endselect * from @T/*id          ziduan1 ziduan2----------- ------- -------1           a1      b22           b1      a2*/
[解决办法]
SQL code
declare @T table (id int,ziduan1 varchar(2),ziduan2 varchar(2))insert into @Tselect 1,'a1','a2' union allselect 2,'b1','b2'union allselect 3,'b1','b2'select * from @Tupdate @T set ziduan2=--selectcase id when 1 then (select ziduan2 from @T where id =2)when 2 then (select ziduan2 from @T where id =1) else ziduan2 endfrom @T where id in(1,2)select * from @T
[解决办法]
想不出更好的办法了
SQL code
declare @T table (  id int,ziduan1 varchar(2),ziduan2 varchar(2))insert into @Tselect 1,'a1','a2' union allselect 2,'b1','b2'SELECT * FROM @TUpdate @T  SET  ziduan2= CASE ziduan2 WHEN 'a2' THEN 'b2 ' WHEN 'b2 ' THEN 'a2 ' ELSE ziduan2 END  WHERE  ziduan2 IN ( 'a2 ', 'b2 ') SELECT * FROM @T/*id          ziduan1 ziduan2 ----------- ------- ------- 1           a1      a22           b1      b2（所影响的行数为 2 行）（所影响的行数为 2 行）id          ziduan1 ziduan2 ----------- ------- ------- 1           a1      b22           b1      a2（所影响的行数为 2 行）*/