sm_emp_update.txt

来自「orale培训教材包括了所有的sql说明和实例」· 文本 代码 · 共 48 行

--先求出0000000007员工总销售额
SELECT SUM(totalprice)/8 FROM sm_saleorderlist 
WHERE employid='0000000007';


















UPDATE sm_emp SET salary 
=(SELECT SUM(totalprice)/8 FROM sm_saleorderlist 
WHERE employid='0000000007')
WHERE empid='0000000007';

--初始sm_saleorderlist无employid='0000000007'的纪录。null的影响
SELECT * FROM sm_emp;

select * from sm_saleorderlist;


--改进,USE NVL()
UPDATE sm_emp SET salary 
=(SELECT NVL(SUM(totalprice),0)/8 FROM sm_saleorderlist 
WHERE employid='0000000007')
WHERE empid='0000000007';

INSERT INTO sm_saleorderlist VALUES(5,100,'0000000007','09_9月_02');
INSERT INTO sm_saleorderlist VALUES(6,10,'0000000007','09_9月_02');

--执行
UPDATE sm_emp SET salary 
=(SELECT NVL(SUM(totalprice),0)/8 FROM sm_saleorderlist 
WHERE employid='0000000007')
WHERE empid='0000000007';

SELECT * FROM sm_emp;