1.创建测试表:
复制代码 代码如下:
drop sequence student_sequence;
create sequence student_sequence start with 10000 increment by 1;
drop table students;
create table students (
id number(5) primary key,
first_name varchar2(20),
last_name varchar2(20),
major varchar2(30),
current_credits number(3),
grade varchar2(2));
insert into students (id, first_name, last_name, major, current_credits,grade)
values (student_sequence.nextval, ‘scott’, ‘smith’, ‘computer science’, 98,null);
insert into students (id, first_name, last_name, major, current_credits,grade)
values (student_sequence.nextval, ‘margaret’, ‘mason’, ‘history’, 88,null);
insert into students (id, first_name, last_name, major, current_credits,grade)
values (student_sequence.nextval, ‘joanne’, ‘junebug’, ‘computer science’, 75,null);
insert into students (id, first_name, last_name, major, current_credits,grade)
values (student_sequence.nextval, ‘manish’, ‘murgratroid’, ‘economics’, 66,null);
commit;
2.查看相应数据
复制代码 代码如下:
sql> select * from students;
id first_name last_name major current_credits gr
———- ——————– ——————– —————————— ————— —
10000 scott smith computer science 98
10001 margaret mason history 88
10002 joanne junebug computer science 75
10003 manish murgratroid economics 66
3.更新语句
复制代码 代码如下:
update students
set grade = (
select grade from
(
select id,
case when current_credits > 90 then ‘a’
when current_credits > 80 then ‘b’
when current_credits > 70 then ‘c’
else ‘d’ end grade
from students
) a
where a.id = students.id
)
/
4.更新后结果
复制代码 代码如下:
sql> select * from students;
id first_name last_name major current_credits gr
———- ——————– ——————– —————————— ————— —
10000 scott smith computer science 98 a
10001 margaret mason history 88 b
10002 joanne junebug computer science 75 c
10003 manish murgratroid economics 66 d