UPDATE student t SET score = (SELECT decode(sex, '男', score + 10, '女', score - 10, score) FROM student WHERE id = t.id)
UPDATE student st SET st.score = (SELECT case when sex = '男' then score + 10 when sex = '女' then score - 10 end FROM student WHERE id = st.id)
SQL> update student set score = decode(sex,'m',score +10,score - 10);
[color=#FF0000]8.0之後的版本用[/color] UPDATE student st SET st.score = (SELECT case when sex = '男' then score + 10 when sex = '女' then score - 10 end FROM student WHERE id = st.id) 之前的呢用UPDATE student t SET score = (SELECT decode(sex, '男', score + 10, '女', score - 10, score) FROM student WHERE id = t.id) update student set score = decode(sex,'m',score +10,score - 10);
UPDATE st SET st.score = (SELECT case when sex = '男' then score + 10 when sex = '女' then score - 10 end FROM students WHERE id = st.id) FROM students st;不好意思,忘了告诉大家,我用的是sqlserver2000。里面没decode这个函数。三楼的代码修改后可以运行了,如上……
create table student(name nvarchar(50),sex nvarchar(5),score int) insert into student select 'a','m',20 union select 'b','m',10 union select 'aa','g',50 union select 'bb','g',60 UPDATE student set score=(case when sex='m' then score+10 else score-10 end) select * from student drop table studentsql2000直接在本身的基础上面update就可以的 不用 再查询出来update
UPDATE student st SET st.score = (SELECT case when sex = '男' then score + 10 when sex = '女' then score - 10 end FROM student WHERE id = st.id)
设置男的值为1,女的为-1,然后
update student set score = score + sex* 10
UPDATE student t
SET score = (SELECT decode(sex, '男', score + 10, '女', score - 10, score)
FROM student
WHERE id = t.id)
UPDATE student st
SET st.score = (SELECT case
when sex = '男' then
score + 10
when sex = '女' then
score - 10
end
FROM student
WHERE id = st.id)
SQL> update student set score = decode(sex,'m',score +10,score - 10);
UPDATE student st
SET st.score = (SELECT case
when sex = '男' then
score + 10
when sex = '女' then
score - 10
end
FROM student
WHERE id = st.id)
之前的呢用UPDATE student t
SET score = (SELECT decode(sex, '男', score + 10, '女', score - 10, score)
FROM student
WHERE id = t.id)
update student set score = decode(sex,'m',score +10,score - 10);
UPDATE st
SET st.score = (SELECT case
when sex = '男' then
score + 10
when sex = '女' then
score - 10
end
FROM students
WHERE id = st.id)
FROM students st;不好意思,忘了告诉大家,我用的是sqlserver2000。里面没decode这个函数。三楼的代码修改后可以运行了,如上……
insert into student
select 'a','m',20 union select 'b','m',10
union select 'aa','g',50 union select 'bb','g',60
UPDATE student set score=(case when sex='m' then score+10 else score-10 end)
select * from student
drop table studentsql2000直接在本身的基础上面update就可以的 不用 再查询出来update
SET st.score = (SELECT case
when sex = '男' then
score + 10
when sex = '女' then
score - 10
end
FROM student
WHERE id = st.id)