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

declare @T table 
(id int,ziduan1 varchar(2),ziduan2 varchar(2))
insert into @T
select 1,'a1','a2' union all
select 2,'b1','b2'

update @T
set ziduan2=
case when ziduan1='a1' then (select ziduan2 from @T where ziduan1='b1')
when ziduan1='b1' then (select ziduan2 from @T where ziduan1='a1')
end

select * from @T
/*
id          ziduan1 ziduan2
----------- ------- -------
1           a1      b2
2           b1      a2
*/

------解决方案--------------------

SQL code

declare @T table 
(id int,ziduan1 varchar(2),ziduan2 varchar(2))
insert into @T
select 1,'a1','a2' union all
select 2,'b1','b2'
union all
select 3,'b1','b2'

select * from @T

update @T set ziduan2=
--select
case id when 1 then (select ziduan2 from @T where id =2)
when 2 then (select ziduan2 from @T where id =1) 
else ziduan2 end
from @T where id in(1,2)

select * from @T

------解决方案--------------------
想不出更好的办法了

SQL code

declare @T table 
(
  id int,ziduan1 varchar(2),ziduan2 varchar(2))
insert into @T
select 1,'a1','a2' union all
select 2,'b1','b2'

SELECT * FROM @T

Update @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      a2
2           b1      b2

（所影响的行数为 2 行）


（所影响的行数为 2 行）

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

（所影响的行数为 2 行）
*/