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

[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 from b where name in (selecet name from a) and code in (select code from a)

这样写不对

[paoluo]

Delete from B Where Exists(Select 1 from A Where name=B.name And code=B.code)

[xwqjamky]

delete from a,b where b.name=a.name and b.code=a.name

[ztfuqing]

and or or ?

[paoluo]

Or

Delete B from B Inner Join A On A.name=B.name And A.code=B.code

Delete B from B,A Where A.name=B.name And A.code=B.code

[paoluo]

二楼的语句是不对的。

[zlp321002]

--测试环境
declare @a表 table(name varchar(5),code int, id int)
insert into @a表 select 'rose',102,1755
union all select 'sai',105,689
union all select 'ayame',238,230

declare @b表 table(name varchar(10),code int, id int,flag bit)
insert into @b表 select 'kisa',106,1655,0
union all select 'sai',105,689,1
union all select 'ayame',238,230,0
union all select 'killua',100,6587,1
--删除环境
delete @b表 from @b表 b
where exists (select 1 from @a表 where name=b.name and code=b.code)
--查看结果
select * from @b表
name code id flag
---------- ----------- ----------- ----
kisa 106 1655 0
killua 100 6587 1

（所影响的行数为 2 行）

[子陌红尘]

delete b from b,a where a.name = b.name and a.code = b.code

or

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

[子陌红尘]

delete b from b,a where a.name = b.name and a.code = b.code

or

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

[vivianfdlpw]

delete b
from b
join a on a.name=b.name and a.code=b.code

or:

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

[zhangyang555]

Delete from B Where Exists(Select 1 from A Where name=B.name And code=B.code)

Delete B from B Inner Join A On A.name=B.name And A.code=B.code
Delete B from B,A Where A.name=B.name And A.code=B.code

都可以正确执行.

[a_thens]

libin_ftsafe(子陌红尘)
这句
delete b from b,a where a.name = b.name and a.code = b.code

你写得跟偶的是一样的啦

可是删掉的好像太多了 本来a表中总共有6k多条纪录 结果要删除的b表中要删掉1w多条

[paoluo]

如果B表中，相同的name和code有多条记录，出现你这种情况就是正常的。

[a_thens]

~~~~~~~~~~~>_<~~~~~~~~~~~~~~~
果然有相同纪录
哭死 他们做数据的说里面没有相同的 我还以为我写错了
害我查了一个下午sql

谢谢大家了

[paoluo]

晕倒