这个sql语句怎么写

[无名小猿]

大家好，假设我有表中有 两个字段 id,seq，context，id和seq为主键，
如果现在的值为：

id seq context
1 1 test1
1 2 test2
现在我想改成
id seq context
1 2 test1
1 1 test2
请问这sql语句怎么写

[十三门徒]

update tb set context=test2 where seq=1 
update tb set context=test1 where seq=2
select * from tb

这样行么？

[无名小猿]

不是这样的，我是想将这两行的seq对调[Quote=引用 1 楼 的回复:]
SQL code
update tb set context=test2 where seq=1
update tb set context=test1 where seq=2
select * from tb

这样行么？
[/Quote]

[yangchun1213]

[Quote=引用 1 楼 的回复:]
SQL code

update tb set context=test2 where seq=1
update tb set context=test1 where seq=2
select * from tb

这样行么？
[/Quote]

哈哈，如果数据多了呢？你也这样改？

[无名小猿]

谢谢各位的回复，我就是希望把某两行的seq对调，

[zdlou]

找一个不可能会出现的值作为临时交换值，如99999999

[让你望见影子的墙]

用触发器。

create trigger t1
on tb
for update
as
begin
update tb
set seq=deleted.seq
from tb join deleted on tb.id=deleted.id and seq!=deleted.seq
end

然后执行
update tb
set seq=(select seq from tb where id=1 and seq=2)

这么一个思路，未经测试

[开着拖拉机泡妞]

--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
create table [test](
[id] int,
[seq] int,
[context] varchar(5),
constraint id_seq_pk primary key([id],[seq])
)
insert [test]
select 1,1,'test1' union all
select 1,2,'test2'

with t
as(
select * from test
)
update test
set seq=t.seq from t
where t.seq<>test.seq

select * from test order by seq desc
/*
id	seq	context
1	2	test1
1	1	test2
*/


--仅仅是一个思路，解决你现在给的这个特殊数据没问题

[Sky_Cat]

select * into #temp from table
update #temp set content=''
update table set content=(select top 1 content from #temp where id=1 and seq=2) where id=1 and seq=1
update table set content=(select top 1 content from #temp where id=1 and seq=1) where id=1 and seq=2
drop table #temp

[Sky_Cat]

select * into #temp from table
--update #temp set content=''
update table set content=(select top 1 content from #temp where id=1 and seq=2) where id=1 and seq=1
update table set content=(select top 1 content from #temp where id=1 and seq=1) where id=1 and seq=2
drop table #temp