create table #(id int,a varchar(10),b int) insert # values(5,'李明',22) insert # values(9,'王风',21) update # set b=(select b from # a where a.id<>#.id)select * from #go drop table #
declare @temp intcreate table #(id int,a varchar(10),b int) insert # values(5,'李明',22) insert # values(9,'王风',21)select @temp=b from # where id=5 update # set b=21 where id=5 update # set b=@temp where id=9select * from #go drop table # --不好意思,现写现卖^_^,这种方法很笨
老大!我那是环境你用 update 你的表 set b=(select b from 你的表 a where a.id<>你的表.id) 你看不懂吗?
To pengdali(大力) ( ) 但是好象不行啊,两个id号在哪表现出来呢?
create table #person(id int,a varchar(10),b int) insert #person values(5,'李明',22) insert #person values(9,'王风',21)--#T 为交换关系表 select * into #T from ( select 5 as f1,9 as f2 ) as Tupdate T set b = isnull((select #person.b from #T,#person where #T.f2 = #person.id and #T.f1 = T.id),(select #person.b from #T,#person where #T.f1 = #person.id and #T.f2 = T.id)) from #person T
------建立你的表 create table #(id int,a varchar(10),b int) insert # values(5,'李明',22) insert # values(9,'王风',21)----------交换数据 update # set b=(select b from # a where a.id<>#.id) ---------显示 select * from # --------删除表 drop table #
create table #(id int,a varchar(10),b int) insert # values(5,'李明',22) insert # values(9,'王风',21)----------交换数据update # set b=(select b from # a where case #.id when 5 then 9 when 9 then 5 end=a.id)---------显示 select * from # --------删除表 drop table #
create table #person(id int,a varchar(10),b int) insert #person values(5,'李明',22) insert #person values(9,'王风',21) insert #person values(2,'赵云',21)select * into #T from ( select 5 as f1,9 as f2 ) as Tselect * from #personupdate T set b = isnull( (select #person.b from #T,#person where #T.f2 = #person.id and #T.f1 = T.id ) ,(select #person.b from #T,#person where #T.f1 = #person.id and #T.f2 = T.id ) ) from #person T where id in (select f1 from #T) or id in (select f2 from #T) select * from #person
这样亦可:update T set b =isnull( isnull( (select #person.b from #T,#person where #T.f2 = #person.id and #T.f1 = T.id ) ,(select #person.b from #T,#person where #T.f1 = #person.id and #T.f2 = T.id ) ),T.b) from #person T--where id in (select f1 from #T) -- or -- id in (select f2 from #T)
少用Update好,不知道你明不明白我的意思,呵呵!
select * into #person from ( select 5 as id,'李明' as a ,22 as b union all select 9,'王风',21 union all select 2,'赵云',21 ) as Tselect * into #T from ( select 5 as f1,9 as f2 union all select 9 ,5 union all select 2 ,9 union all select 10 ,1 ) as Tselect * from #personupdate T set b = isnull( (select max(#person.b) from #T,#person where #T.f2 = #person.id and #T.f1 = T.id ) ,(select min(#person.b ) from #T,#person where #T.f1 = #person.id and #T.f2 = T.id ) ) from #person T where id in (select f1 from #T union select f2 from #T) select * from #person
这样的意思么设table(cid,a,b)update table
set a=c.b,b=c.a
from (select * from table) c
where c.cid=3 and table.cid=4
5,李明,21
9,王风,22
想用一条语句把记录改成:
5,李明,22
9,王风,21
要交换的两条记录的id是知道的,谢谢大家!
insert # values(5,'李明',22)
insert # values(9,'王风',21)
update # set b=(select b from # a where a.id<>#.id)select * from #go
drop table #
最好能给我一个直接能用的语句,谢谢各位了。还有一个问题也没解决,各位大侠能否顺便看看:
http://expert.csdn.net/Expert/topic/1651/1651270.xml?temp=.011944
a = b,
b = a
insert # values(5,'李明',22)
insert # values(9,'王风',21)select @temp=b from # where id=5
update # set b=21 where id=5
update # set b=@temp where id=9select * from #go
drop table #
--不好意思,现写现卖^_^,这种方法很笨
update 你的表 set b=(select b from 你的表 a where a.id<>你的表.id)
你看不懂吗?
但是好象不行啊,两个id号在哪表现出来呢?
insert #person values(5,'李明',22)
insert #person values(9,'王风',21)--#T 为交换关系表
select * into #T
from
(
select 5 as f1,9 as f2
) as Tupdate T
set b = isnull((select #person.b from #T,#person where #T.f2 = #person.id and #T.f1 = T.id),(select #person.b from #T,#person where #T.f1 = #person.id and #T.f2 = T.id))
from #person T
create table #(id int,a varchar(10),b int)
insert # values(5,'李明',22)
insert # values(9,'王风',21)----------交换数据
update # set b=(select b from # a where a.id<>#.id)
---------显示
select * from #
--------删除表
drop table #
insert # values(5,'李明',22)
insert # values(9,'王风',21)----------交换数据update # set b=(select b from # a where case #.id when 5 then 9 when 9 then 5 end=a.id)---------显示
select * from #
--------删除表
drop table #
insert #person values(5,'李明',22)
insert #person values(9,'王风',21)
insert #person values(2,'赵云',21)select * into #T
from
(
select 5 as f1,9 as f2
) as Tselect * from #personupdate T
set b = isnull( (select #person.b
from #T,#person
where #T.f2 = #person.id
and #T.f1 = T.id
)
,(select #person.b
from #T,#person
where #T.f1 = #person.id
and #T.f2 = T.id
)
)
from #person T
where id in (select f1 from #T)
or
id in (select f2 from #T) select * from #person
set b =isnull( isnull( (select #person.b
from #T,#person
where #T.f2 = #person.id
and #T.f1 = T.id
)
,(select #person.b
from #T,#person
where #T.f1 = #person.id
and #T.f2 = T.id
)
),T.b)
from #person T--where id in (select f1 from #T)
-- or
-- id in (select f2 from #T)
from
(
select 5 as id,'李明' as a ,22 as b
union all
select 9,'王风',21
union all
select 2,'赵云',21
) as Tselect * into #T
from
(
select 5 as f1,9 as f2
union all
select 9 ,5
union all
select 2 ,9
union all
select 10 ,1
) as Tselect * from #personupdate T
set b = isnull( (select max(#person.b)
from #T,#person
where #T.f2 = #person.id
and #T.f1 = T.id
)
,(select min(#person.b )
from #T,#person
where #T.f1 = #person.id
and #T.f2 = T.id
)
)
from #person T
where id in (select f1 from #T
union
select f2 from #T) select * from #person