delete b where b.name|| b.code in (select a.name|| a.code from a)
delete from b where exists(select 1 from a where a.name = b.name and a.code = b.code) ------------------- 这样会删掉b表所有记录吗,搂主有没试过啊SQL> select * from test1; ID USERID ---------- -------------------- 1 user001 4 user004 6 user006SQL> select * from test2; ID USERID ---------- ---------- 1 user004 2 user002 5 user002 6 user006SQL> delete from test2 b where exists(select 1 from test1 a where a.id = b.id and a.userid = b.userid);1 row deletedSQL> select * from test2; ID USERID ---------- ---------- 1 user004 2 user002 5 user002
另一种方法,不过要注意加上从表的约束条件 没有加约束条件的情况:SQL> delete from (select a.* from test2 b,test1 a where a.id = b.id and a.userid = b.userid);delete from (select a.* from test2 b,test1 a where a.id = b.id and a.userid = b.userid)ORA-01752: 不能从没有一个键值保存表的视图中删除增加唯一性约束条件:SQL> alter table TEST1 add constraint KEYA unique (ID,USERID);Table altered前面的是主表,也就是要删除记录的表 SQL> delete from (select b.* from test2 b,test1 a where a.id = b.id and a.userid = b.userid);1 row deleted
delete b where (name,code) in (select b.name,b.code from a where a.name=b.name and a.code=b.code); delete b where (name,code) exists(select b.name,b.code from a where a.name=b.name and a.code=b.code);
会出现全选的情况的:SQL> select a1,a2 from a;A1 A2 ---------- ---------- 1 1 1 2 1 3 2 1 2 2 3 1 3 2 3 4 3 39 rows selectedExecuted in 0.016 secondsSQL> select d2,d3 from d;D2 D3 ---------- ---------- 2 2 2 1 4 1Executed in 0.016 secondsSQL> select d2,d3 from d where exists(select 1 from a,d where a1=d2 and a2=d3);D2 D3 ---------- ---------- 2 2 2 1 4 1Executed in 0.015 secondsSQL>
SQL> select d2,d3 from d where (d2,d3) in (select d2,d3 from d,a where a1=d2 and a2=d3);D2 D3 ---------- ---------- 2 1 2 2Executed in 0.016 secondsSQL> 这样就没有问题。
(select b.id from a,b where a.name = b.name and a.code=b.code);前提是保证id是唯一性的。
DELETE FROM b WHERE EXISTS (SELECT * FROM a WHERE b.name= a.name and b.code=a.code) 这样会选出 b表 中的全部纪录
delete b where id in
(select b.id from a,b where a.name = b.name and a.code=b.code);前提是保证id是唯一性的。
这个我用过 不行的 里面没有id是唯一的 选出的记录比我要的多
(select b.name,b.code from a,b where a.name = b.name and a.code=b.code);这样可以。
SQL> select * from a;A1 A2 A3
---------- ---------- ----------
1 1 ??
1 2 ??
1 3 ??
2 1 ??
2 2 ??
3 1 ??1
3 2 ??2
3 4 ??4
3 3 ??39 rows selectedExecuted in 0.016 secondsSQL> select * from d;D1 D2 D3 D4 D5
---------- ---------- ---------- ---------- ----------
000001 2 2 1 20050801
000002 2 1 2 20050724
000003 4 1 3 20050803Executed in 0.016 secondsSQL> select * from d where (d2,d3) in (select d2,d3 from d,a where a1=d2 and a2=d3);D1 D2 D3 D4 D5
---------- ---------- ---------- ---------- ----------
000002 2 1 2 20050724
000001 2 2 1 20050801Executed in 0.015 secondsSQL>
如果会选出 b表 中的全部纪录,那就说明b表中的全部记录都是要删除的
where b.name|| b.code in (select a.name|| a.code from a)
-------------------
这样会删掉b表所有记录吗,搂主有没试过啊SQL> select * from test1; ID USERID
---------- --------------------
1 user001
4 user004
6 user006SQL> select * from test2; ID USERID
---------- ----------
1 user004
2 user002
5 user002
6 user006SQL> delete from test2 b where exists(select 1 from test1 a where a.id = b.id and a.userid = b.userid);1 row deletedSQL> select * from test2; ID USERID
---------- ----------
1 user004
2 user002
5 user002
没有加约束条件的情况:SQL> delete from (select a.* from test2 b,test1 a where a.id = b.id and a.userid = b.userid);delete from (select a.* from test2 b,test1 a where a.id = b.id and a.userid = b.userid)ORA-01752: 不能从没有一个键值保存表的视图中删除增加唯一性约束条件:SQL> alter table TEST1 add constraint KEYA unique (ID,USERID);Table altered前面的是主表,也就是要删除记录的表
SQL> delete from (select b.* from test2 b,test1 a where a.id = b.id and a.userid = b.userid);1 row deleted
delete b where (name,code) exists(select b.name,b.code from a where a.name=b.name and a.code=b.code);
---------- ----------
1 1
1 2
1 3
2 1
2 2
3 1
3 2
3 4
3 39 rows selectedExecuted in 0.016 secondsSQL> select d2,d3 from d;D2 D3
---------- ----------
2 2
2 1
4 1Executed in 0.016 secondsSQL> select d2,d3 from d where exists(select 1 from a,d where a1=d2 and a2=d3);D2 D3
---------- ----------
2 2
2 1
4 1Executed in 0.015 secondsSQL>
---------- ----------
2 1
2 2Executed in 0.016 secondsSQL> 这样就没有问题。
只是执行速度好慢哦 谢谢大家啦~~~~~~~~~~~~~~~
那个 select 1 from ..... 这个 1 是什么意思呢???