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') endselect * from @T /* id ziduan1 ziduan2 ----------- ------- ------- 1 a1 b2 2 b1 a2 */
update tablename set ziduan2= case when ziduan1='a1' then (select ziduan2 from tablename where ziduan1='b1') when ziduan1='b1' then (select ziduan2 from tablename where ziduan1='a1') end
这个你把其他记录都置为null了吧
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 @Tupdate @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
想不出更好的办法了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 @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 a2 2 b1 b2(所影响的行数为 2 行) (所影响的行数为 2 行)id ziduan1 ziduan2 ----------- ------- ------- 1 a1 b2 2 b1 a2(所影响的行数为 2 行) */
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')
endselect * from @T
/*
id ziduan1 ziduan2
----------- ------- -------
1 a1 b2
2 b1 a2
*/
update tablename
set ziduan2=
case when ziduan1='a1' then (select ziduan2 from tablename where ziduan1='b1')
when ziduan1='b1' then (select ziduan2 from tablename where ziduan1='a1')
end
这个你把其他记录都置为null了吧
(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 @Tupdate @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
(
id int,ziduan1 varchar(2),ziduan2 varchar(2))
insert into @T
select 1,'a1','a2' union all
select 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 a2
2 b1 b2(所影响的行数为 2 行)
(所影响的行数为 2 行)id ziduan1 ziduan2
----------- ------- -------
1 a1 b2
2 b1 a2(所影响的行数为 2 行)
*/