sql如何删除一个表中某列列值和另一表某列列值相同的所有行
sql如何删除一个表中某列列值和另一表某列列值相同的所有行？
比如A表中的PSG_NAME和B表中的RSP_NAME有相同的姓名，去掉A表中的这些行。。。。。
sql语句该怎么写呢？
[最优解释]
null
[其他解释]
delete from A where psg_name in (select psg_name from b)
[其他解释]
delete from A where exists(select 1 from b where a.psg_name =b.psg_name)
create table tb(id int,val varchar(10))
insert into tb select 1,'a' union all select 2,'b' union all select 3,'c'
create table tb1(id int,val varchar(10))
insert into tb1 select 2,'a' union all select 4,'b' union all select 6,'c'
go
delete a from tb a inner join tb1 b on a.id=b.id
select * from tb
/*
id val
----------- ----------
1 a
3 c
(2 行受影响)
*/
go
drop table tb,tb1
delete from A where exists(
select 1 from B where a.PSG_NAME=B.RSP_NAME
)
delete from A where exists(select 1 from b where a.psg_name =b.rsp_name)
