交换数据中的两条记录值

[fengylm]

如题
比如说我有个表T，字段为 field1，field2数据如下，其中field1为主键列
field1 field2
1 数据1
2 数据2

怎么写SQL语句可以交换field1中两个值得到如下
field1 field2
2 数据1
1 数据2

[hyde100]

直接更新不就行了吗？

[Garnett_KG]

--两笔记录就这样
UPDATE  T
SET     Field2=b.Field2
FROM    T a ,T b
WHERE   a.Field1<>b.Field1

SELECT * FROM T ORDER BY Field1 DESC

[水族杰纶]

update tb set field1=99999 where field1=1
update tb set field1=1 where field1=2
update tb set field1=2 where field1=99999

[hyde100]

楼主应该说些实际的条件，有这样的需求肯定是不止这两条数据

[chuifengde]

update [Table] set field2=(select field2 from [Table] a where field1<>[Table].field1 and field1 in(1,2))
where field1 in (1,2)

[等不到来世]

declare @id1 int,@id2 int --要交换的两条记录的主键
set @id1=1
set @id2=2

update tb
set field2=(select field2 from tb where id=case a.id when @id1 then @id2 else @id1 end)
from tb a
where id=@id1 or id=@id2

[等不到来世]

写简洁一点。。

update tb
set field2=(select field2 from tb where id=case tb.id when @id1 then @id2 else @id1 end)
where id=@id1 or id=@id2

[jiang5311]

update [Table] set field2=(select field2 from [Table] a where field1<>[Table].field1 and field1 in(1,2))
where field1 in (1,2)

[tianhuo_soft]

update [Table]
 set field2 in (select field2 from [Table] a where field1 <>[Table].field1 and field1 in(1,2)) 
where field1 in (1,2)