Update一个表格里的属性的问题
2个表格如下:
CREATE TABLE Part( /* Part description */
PNumber NUMBER(10) NOT NULL, /* Number */
PName VARCHAR(30) NOT NULL, /* Name */
PManufacturer VARCHAR(255) NOT NULL, /* Manufacturer */
PPrice NUMBER(7,2) NOT NULL, /* Price */
PRating VARCHAR(10) NULL, /* Rating */
CONSTRAINT Part_pkey PRIMARY KEY( PNumber ),
CONSTRAINT Part_fkey1 FOREIGN KEY( PRating )
REFERENCES LRatings( Rating )
);
CREATE TABLE OrderLine( /* Ordered parts */
LOrder NUMBER(10) NOT NULL, /* Order number */
LLine NUMBER(3) NOT NULL, /* Line number */
LPart NUMBER(10) NOT NULL, /* Part number */
LQuantity NUMBER(6) NOT NULL, /* Quantity */
CONSTRAINT OrderLine_pkey PRIMARY KEY( LOrder, LLine ),
CONSTRAINT OrderLine_fkey1 FOREIGN KEY( LPart )
REFERENCES Part( PNumber ),
CONSTRAINT OrderLine_fkey2 FOREIGN KEY( LOrder )
REFERENCES Orders( ONumber )
);
需求：更新PRating,当LQuantity等于0时，为NULL；小于等于100并且大于0时，为RARE；小于等于1000并大于100时，为NORMAL;其他情况为FREQUENT。
我本来想用：Update Part SET PRating = NULL WHERE~~~~~~~~~,因为我想在一句话内完成以上要求，大哥们，怎么才能在UPDATE里完成这个分支语句呢？


------解决方案--------------------
SQL> select pnumber,prating from part;

PNUMBER PRATING
---------- ----------
0
100
1000
2000

SQL> select * from OrderLine;

LORDER LLINE LPART LQUANTITY
---------- ---------- ---------- ----------
0 0 0 0
100 100 100 100
1000 10 1000 1000
2000 10 2000 2000
SQL> update part set prating=(
2 select case when a.LQUANTITY=0 then null
3 when a.LQUANTITY<=100 then 'rare'
4 when a.LQUANTITY<=1000 then 'normal'
5 else 'frequent'
6 end
7 from OrderLine a where a.LPart=part.pnumber);

已更新4行。
SQL> select pnumber,prating from part;

PNUMBER PRATING
---------- ----------
0
100 rare
1000 normal
2000 frequent

SQL>
------解决方案--------------------

探讨

我尝试了wfffc的做法，怎么会有这样的报错呢：ERROR at line 2:
ORA-01427: single-row subquery returns more than one row