一个update语句写法
pinfo表
pno pname
1 n1
2 n2
3 n3
4 n4
5 n5
6 n6
einfo表
eno pno ename eage esalary
101 1 e101 30 700
102 1 e102 30 800
103 1 e103 30 900
104 4 e104 40 1000
105 5 e105 40 1100
106 6 e106 60 1200
根据pname= 'n1 '这个信息来,更新einfo表中的esalary加10%
在oralce中如何写udate语句???
注:oralce中update语句之后不让用from
-- 关联语句需验证
UPDATE eInfo
SET esalary = esalary * 1.1
FROM pinfo p INNER JOIN
einfo e ON p.pno = e.pno
WHERE pname = 'n1 '
//***用IN关键字***
UPDATE eInfo SET eInfo.esalary = eInfo.esalary * 1.1
WHERE (eInfo.pno IN (SELECT pinfo.pno FROM pinfo WHERE pinfo.pname = 'n1 '))
//***用EXISTS关键字***
UPDATE eInfo SET esalary = esalary * 1.1
WHERE EXISTS (SELECT 1 FROM pinfo WHERE pinfo.pno = einfo.pno AND pinfo.pname = 'n1 ')
3 rows updated//right
//***以下的结果是错误的***
UPDATE eInfo SET esalary = esalary * 1.1
WHERE EXISTS (SELECT * FROM pinfo,einfo//此处要注意,多了einfo结果就错了
WHERE pinfo.pno = einfo.pno AND pinfo.pname = 'n1 ')
6 rows updated//error
总结:利用in(not in),exists(not exists)来转换from子句,delete语句同理。
本文来源:https://www.2haoxitong.net/k/doc/64a40f651711cc7931b716cb.html