Question

SQL语句中交换某个字段的记录值 的SQL语句在线急等

在这个表中，交换sa1，Score为10和sa2，Score为60的VALUE字段值 怎么做 SQL语句 谢谢 在线急等

Answer 1

select t1.uname,t1.scoure , (case t1.uname when 'sa1' then t2.value end )

from tablename2 t1,tablename2 t2
where t1.uname='sa1'
and t1.scoure=100
and t2.uname='sa2'
and t2.scoure=60
union all
select t2.uname, t2.scoure , (case t2.uname when 'sa2' then t1.value end )
from tablename2 t1,tablename2 t2
where t1.uname='sa1'
and t1.scoure=100
and t2.uname='sa2'
and t2.scoure=60

追问

出现别名的地方变红

追答

from ta t,ta t1 这样才对啊

Answer 2

DECLARE
--定义两个变量用于接收两个value值
v_1 number(2);
v_2 number(2);
BEGIN
SELECT value into v_1 FROM info WHERE uname='sa1' and score=100;
SELECT value into v_2 FROM info WHERE uname='sa2' and score=60;
--更新表 交换value值
update info set value=v_2 WHERE uname='sa1' and score=100;
update info set value=v_1 WHERE uname='sa2' and score=60;

END;

Answer 3

你说的sa1，score是10还是100？

而且不管10或者100，那假如sa1中的score有多个，或sa2中的score有多个是60，如何判断哪个和哪个交换。

追问

那如果是只有两个字段SA1 SCORE100 SA2 SCORE是60的 他们的VALUE值不同 那怎么交换他们两个的VALUE值

追答

你这样说不就好弄了，稍等哦。

假如咱们不引入中间字段，即外部变量。
并假设该表名称为ta,所有字段都是字符，如果你的score是数字型，把下面数字的单引号去掉就可以。

update ta
set value =
(select t.value - t1.value from ta t, ta t1
where t1.uname = 'sa2'
and t.uname = 'sa1'
and t1.score = '60'
and t.score = '100')
where uname = 'sa1'
and score = '100';
update ta
set value =
(select t1.value + t.value from ta t, ta t1
where t1.uname = 'sa2'
and t.uname = 'sa1'
and t1.score = '60'
and t.score = '100')
where uname = 'sa2'
and score = '60';
update ta
set value =
(select t1.value - t.value from ta t, ta t1
where t1.uname = 'sa2'
and t.uname = 'sa1'
and t1.score= '60'
and t.score = '100')
where uname = 'sa1'
and score = '100';

如果有错误，继续问我。我等你。

追问

t1 和t是什么

追答

是ta这个表的别名，你只要把ta替换成你这个表的表名就可以了。
或者你直接告诉我你这个表名是什么

追问

表名是Info 我不用理T1 T2 只要把TA替换成表名就行了吗

追答

对头！你太聪明了。

大哥，你试试我的能不能用嘛。我还等你结果呢。
如果你喜欢引入变量的方式也可以，我前面就说了这事不引入变量的方式。

Answer 4

挺简单的事情，搞那么复杂
declare
@v_tt1 int
@v_tt2 int
select @v_tt1=Value from table1 where UName = 'sa1' and Score = 100
select @v_tt2=Value from table2 where UName = 'sa2' and Score = 60
update table1 set Value =@v_tt2 where UName = 'sa1' and Score = 100
update table1 set Value = @v_tt1 where UName = 'sa2' and Score = 60

追问

@v_tt1 int
是什么

追答

定义的临时变量

Answer 5

declare @temp int
select @temp=[Value] from Info where UName = 'sa1' and Score = 10

update Info set [Value] = (
select [Value] from Info where UName = 'sa2' and Score = 60
) where UName = 'sa1' and Score = 10

update Info set [Value] = @temp where UName = 'sa2' and Score = 60