delete t1 from t1 a where a.id in (select b.id from t2 b where b.id3 = '1') and a.id2 in (select b.id2 from t2 b where b.id3 = '1')
hippie1024(努力必有痕迹) : EX1,EX2 语法有错吗?我在SQL SERVER 中测试过. -------------------------------------------------------------------- wypudong(叶叶) ( ) : delete t1 from t1 a where a.id in (select b.id from t2 b where b.id3 = '1') and a.id2 in (select b.id2 from t2 b where b.id3 = '1') 两个 in 的限定怎么确定唯一记录呢???? --------------------------------------------------------------------
EX5 不一定对,要看(select b.col2 from t2 b where a.id = b.id and a.id2 = b.id2 and b.id3 = '1')返回的是单条记录还是多条
继续求解ING,肯定要保证是唯一记录吧,否则EX3 EX4 EX5 应该都错了.应该是这样把?以前用SQL SERVER 请多执教.
EX5:update t1 a set col1 = (select b.col2 from t2 b where a.id = b.id and a.id2 = b.id2 and b.id3 = '1') where exists(select 1 from t2 b where a.id = b.id and a.id2 = b.id2 and b.id3 = '1')也不一定正确,要确保select b.col2 from t2 b where a.id = b.id and a.id2 = b.id2 and b.id3 = '1'是唯一的.
EX1,EX2 在SQL SERVER 中是可以,不过这种语法ORACLE中不支持。EX5的正确性的确要看select b.col2 from t2 b where a.id = b.id and a.id2 = b.id2 and b.id3 = '1'是否唯一。 不过如果不唯一,你的SQL就没有意义了
1.那么 EX1 和 EX4 是等价的了. 2.EX3 是语法错误吗? 3.如果我保证select b.col2 from t2 b where a.id = b.id and a.id2 = b.id2 and b.id3 = '1'是唯一.那么 EX5是否又可以写成这样呢: EX6: update t1 a set col1 = (select b.col2 from t2 b where a.id = b.id and a.id2 = b.id2 and b.id3 = '1') 4.ORACLE有没有相关操作多表的文档呢?
1,EX1是SQLSERVER中的写法,在ORACLE中语法就不支持,EX4在ORACLE中可以(等价于EX1在SQLSERVER中) 2,EX3不是语法错,是逻辑错,exists判断存在性,而你后面的SQL结果的存在性不依赖于t1 的记录 它只会删除你所有的数据 3,不能,除非你保证select b.col2 from t2 b where a.id = b.id and a.id2 = b.id2 and b.id3 = '1'不仅唯一,而且要必需存在,否则某些值会被更新成NULL 4,先把SQL Reference看看吧: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/toc.htm
a.id2 in (select b.id2 from t2 b where b.id3 = '1')
EX1,EX2 语法有错吗?我在SQL SERVER 中测试过.
--------------------------------------------------------------------
wypudong(叶叶) ( ) :
delete t1 from t1 a where a.id in (select b.id from t2 b where b.id3 = '1') and
a.id2 in (select b.id2 from t2 b where b.id3 = '1')
两个 in 的限定怎么确定唯一记录呢????
--------------------------------------------------------------------
不过如果不唯一,你的SQL就没有意义了
我的本意是想把sql server 中的 EX1,EX2 转换到ORACLE 中.
那么EX3 和 EX4 是等价的了?
---------------------------------------------------------
GerryYang(轻尘) :
如果EX5 不对,那么要如何写才能和 EX2 等价呢?
EX4是可以的EX5 还没明白么?
只要你的T2的ID,ID2,id3 = '1'三个条件可以唯一定位到一条记录上就没问题了,
你可以distinct b.col2 ,
如果还不唯一,你的更新就没有意义,因为在更新T1中一条记录时会有多种可能的值,这在逻辑上就是不合理的。
2.EX3 是语法错误吗?
3.如果我保证select b.col2 from t2 b where a.id = b.id and a.id2 = b.id2 and b.id3 = '1'是唯一.那么
EX5是否又可以写成这样呢:
EX6:
update t1 a set col1 = (select b.col2 from t2 b where a.id = b.id and a.id2 = b.id2 and b.id3 = '1')
4.ORACLE有没有相关操作多表的文档呢?
2,EX3不是语法错,是逻辑错,exists判断存在性,而你后面的SQL结果的存在性不依赖于t1 的记录
它只会删除你所有的数据
3,不能,除非你保证select b.col2 from t2 b where a.id = b.id and a.id2 = b.id2 and b.id3 = '1'不仅唯一,而且要必需存在,否则某些值会被更新成NULL
4,先把SQL Reference看看吧:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/toc.htm