delete 表名 a where exists(select 1 from 另一个表 where 字段1=a.字段1 and 字段2=a.字段2)
delete 表 where exists(select 1 from 另一表 where id=表.id)--id是两表的主键
delete * from tb1 where exists(select 1 from tb2 where tb.id=tb2.id)
DELETE A FROM tb AS A WHERE EXISTS(SELECT * FROM ta WHERE id = A.id);
delete ta from tb where ta.col=tb.col
declare @tablea table(id int,col varchar(20)) insert into @tablea select 1,'b' union all select 2,'c' union all select 3,'e' union all select 4,'f'declare @tableb table(id int,col varchar(20)) insert into @tableb select 1,'b' union all select 2,'d' union all select 3,'g' union all select 4,'f'--删除@tableb中在@tablea中重复的数据 delete @tableb from @tablea a left join @tableb b on a.id=b.id and a.col=b.col where b.id is not null
select * from @tableb
/* id col ----------- -------------------- 2 d 3 g */
delete #a where exists(select 1 from #b where id=#a.id)
我想删除表中与另外一表相同的数据,请SQL语句如何写?delete a from 表1 a join 表b b on a.id=b.id
--查询A表不在B表中存在的记录 select a.* from #a a(nolock) where not exists(select 1 from #b where lshh=a.lshh)
--查询B表不在A表中存在的记录 select a.* from #b a(nolock) where not exists(select 1 from #a where lshh=a.lshh)
--查询A表不在B表中存在的记录 select a.* from #a a(nolock) where not exists(select 1 from #b where lshh=a.lshh and hw=a.hw and spid=a.spid)
--查询B表不在A表中存在的记录 select a.* from #b a(nolock) where not exists(select 1 from #a where lshh=a.lshh and hw=a.hw and spid=a.spid)修改一下即可
delete 表名 a where exists(select 1 from 另一个表 where 主键字段=a.主键字段)
where exists(select 1 from tb2 where tb.id=tb2.id)
FROM tb AS A
WHERE EXISTS(SELECT * FROM ta WHERE id = A.id);
declare @tablea table(id int,col varchar(20))
insert into @tablea
select 1,'b' union all
select 2,'c' union all
select 3,'e' union all
select 4,'f'declare @tableb table(id int,col varchar(20))
insert into @tableb
select 1,'b' union all
select 2,'d' union all
select 3,'g' union all
select 4,'f'--删除@tableb中在@tablea中重复的数据
delete @tableb
from @tablea a left join @tableb b on a.id=b.id and a.col=b.col
where b.id is not null
select * from @tableb
/*
id col
----------- --------------------
2 d
3 g
*/
on a.id=b.id
select a.*
from #a a(nolock)
where not exists(select 1 from #b where lshh=a.lshh)
--查询B表不在A表中存在的记录
select a.*
from #b a(nolock)
where not exists(select 1 from #a where lshh=a.lshh)
select a.*
from #a a(nolock)
where not exists(select 1 from #b where lshh=a.lshh and hw=a.hw and spid=a.spid)
--查询B表不在A表中存在的记录
select a.*
from #b a(nolock)
where not exists(select 1 from #a where lshh=a.lshh and hw=a.hw and spid=a.spid)修改一下即可