update einfo set esalary = esalary * 1.1 where (select 1 from pinfo where pinfo.pno=einfo.pno and pinfo.pname='n1' )
解决方案 »
- VC6.0如何获取ORACLE 11g中NUMBER(38)中的字段值
- pro*c 能够获取 游标变量 对应的 descriptor吗?
- trigger能批量生成吗
- 制作一个WEB的报表,何种方法效率最高?
- 求高手帮忙优化存储过程
- 求对一个允许重复记录的表做统计,对于重复的记录sum的时候只要算1个,内详!
- 请教高手:在oracle程序中如何获取exception的错误信息
- ORACLE的导入导出?
- 急!在redhat linux8下安装 oracle9i出现 Error loading native library: libnjni9.so.怎么办呢??
- 如何能动态的获取视图的字段名、数据,需要将数据拼接成JSON格式
- 我看资料,这两个语句都是查表空间的大小,查出来相差怎么那么大呢?哪个更准确?
- 登录问题
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语句同理。