怎么样从一个表中删掉与另一个表中字段相同的纪录

[a_thens]

比如有两个表
a表
name code id

rose 102 1755
sai 105 689
ayame 238 230


b表
name code id flag

kisa 106 1655 0
sai 105 689 1
ayame 238 230 0
killua 100 6587 1

要从b表中删掉 与a中字段 name，code相同的纪录

delete b from b,a where a.name = b.name and a.code = b.code
delete from b,a where a.name = b.name and a.code = b.code
select * from a Where Exists(Select 1 from a,bWhere a.name=b.name And a.code = b.code)


这样写不对 总是提示错误

[沝林]

delete from b where exists(select 1 from a where a.name = b.name and a.code = b.code)

[sasacat]

DELETE FROM b

WHERE EXISTS (SELECT *

FROM a

WHERE b.name= a.name and b.code=a.code)

[feng2]

delete b where id in
(select b.id from a,b where a.name = b.name and a.code=b.code);

前提是保证id是唯一性的。

[a_thens]

sasacat(傻傻猫) ( ) 信誉：100 2005-08-31 09:39:00 得分: 0


DELETE FROM b

WHERE EXISTS (SELECT *

FROM a

WHERE b.name= a.name and b.code=a.code)

这样会选出 b表 中的全部纪录

[a_thens]

feng2(蜀山风云) ( ) 信誉：100 2005-08-31 09:43:00 得分: 0


delete b where id in
(select b.id from a,b where a.name = b.name and a.code=b.code);

前提是保证id是唯一性的。


这个我用过 不行的 里面没有id是唯一的 选出的记录比我要的多

[a_thens]

select b.* from a,b where b.name = a.name and b.code = a.code

这样选出来的纪录是对的 但是偶不知道怎么删掉 b 中的这些纪录

[feng2]

delete b where (name,code) in
(select b.name,b.code from a,b where a.name = b.name and a.code=b.code);

这样可以。

[feng2]

这是例子：


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 ??3

9 rows selected

Executed in 0.016 seconds

SQL> select * from d;

D1 D2 D3 D4 D5
---------- ---------- ---------- ---------- ----------
000001 2 2 1 20050801
000002 2 1 2 20050724
000003 4 1 3 20050803

Executed in 0.016 seconds

SQL> 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 20050801

Executed in 0.015 seconds

SQL>

[waterfirer]

duanzilin(寻)和sasacat(傻傻猫) 的结果是一样的，
如果会选出 b表 中的全部纪录，那就说明b表中的全部记录都是要删除的

[小李木耳]

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 user006

SQL> select * from test2;

ID USERID
---------- ----------
1 user004
2 user002
5 user002
6 user006

SQL> delete from test2 b where exists(select 1 from test1 a where a.id = b.id and a.userid = b.userid);

1 row deleted

SQL> 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

[dnmyg2003]

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);

[feng2]

会出现全选的情况的：

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 3

9 rows selected

Executed in 0.016 seconds

SQL> select d2,d3 from d;

D2 D3
---------- ----------
2 2
2 1
4 1

Executed in 0.016 seconds

SQL> 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 1

Executed in 0.015 seconds

SQL>

[feng2]

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 2

Executed in 0.016 seconds

SQL>

这样就没有问题。

[a_thens]

好复杂哦 大家的几乎都对啦
只是执行速度好慢哦

谢谢大家啦~~~~~~~~~~~~~~~


那个 select 1 from ..... 这个 1 是什么意思呢？？？

[沝林]

select 1... 这里1是个常数列，因为这里你没必要选出结果集中任何列，就可以用常数列，你可以用任何其他字符代替，例如'a'

[a_thens]

恩 谢谢你

[a_thens]

怎么不能给分了