【www.tuzhexing.com--推荐】
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