Update einfo Set einfo.esalary=einfo.esalary*1.1 Where einfo.pno In (Select pinfo.pno From pinfo Where pinfo.pName='n1')
to huangyan168168(hy) 多谢指教!UPDATE eInfo SET esalary = esalary * 1.1 FROM pinfo p INNER JOIN einfo e ON p.pno = e.pno WHERE pname = 'n1'其等效语句:UPDATE eInfo SET esalary = esalary * 1.1 WHERE (pno IN (SELECT pno FROM pinfo WHERE pname = 'n1'))总结:利用in(not in)来转换from子句
SQL> select * from einfo; ENO PNO SALARY ---- --- ----------- 101 1 770 102 1 880 103 1 990 104 4 1100 105 5 1210 106 6 13206 rows selectedExecuted in 0.016 secondsSQL> select * from pinfo;PNO PNAME --- ---------- 1 n1 2 n2 3 n3 4 n4 5 n5 6 n66 rows selectedExecuted in 0.031 secondsSQL> update einfo set salary = salary * 1.1 where exists(select * from einfo,pinfo where einfo.pno = pinfo.pno and pinfo.pname = 'n1');6 rows updatedExecuted in 0 seconds
to huangyan168168(hy) to czj68586(阿草) 多谢指教!UPDATE eInfo SET esalary = esalary * 1.1 FROM pinfo p INNER JOIN einfo e ON p.pno = e.pno WHERE pname = 'n1'其等效语句:UPDATE eInfo SET eInfo.esalary = eInfo.esalary * 1.1 WHERE (eInfo.pno IN (SELECT pinfo.pno FROM pinfo WHERE pinfo.pname = 'n1'))总结:利用in(not in)来转换from子句,delete语句同理。
等效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 * 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语句同理。
Set einfo.esalary=einfo.esalary*1.1 Where einfo.pno In
(Select pinfo.pno From pinfo Where pinfo.pName='n1')
多谢指教!UPDATE eInfo
SET esalary = esalary * 1.1
FROM pinfo p INNER JOIN
einfo e ON p.pno = e.pno
WHERE pname = 'n1'其等效语句:UPDATE eInfo
SET esalary = esalary * 1.1
WHERE (pno IN
(SELECT pno
FROM pinfo
WHERE pname = 'n1'))总结:利用in(not in)来转换from子句
---- --- -----------
101 1 770
102 1 880
103 1 990
104 4 1100
105 5 1210
106 6 13206 rows selectedExecuted in 0.016 secondsSQL> select * from pinfo;PNO PNAME
--- ----------
1 n1
2 n2
3 n3
4 n4
5 n5
6 n66 rows selectedExecuted in 0.031 secondsSQL> update einfo set salary = salary * 1.1 where exists(select * from einfo,pinfo where einfo.pno = pinfo.pno and pinfo.pname = 'n1');6 rows updatedExecuted in 0 seconds
to czj68586(阿草)
多谢指教!UPDATE eInfo
SET esalary = esalary * 1.1
FROM pinfo p INNER JOIN
einfo e ON p.pno = e.pno
WHERE pname = 'n1'其等效语句:UPDATE eInfo
SET eInfo.esalary = eInfo.esalary * 1.1
WHERE (eInfo.pno IN
(SELECT pinfo.pno
FROM pinfo
WHERE pinfo.pname = 'n1'))总结:利用in(not in)来转换from子句,delete语句同理。
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 * 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语句同理。