delete from b where not exists(select 1 from a where [column A]=b.[column B])
delete from B表 b where not exists (select 1 from A表 a where a.columnA=b.columnB)
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2012-07-17 13:58:50 -- Version: -- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) -- Apr 22 2011 11:57:00 -- Copyright (c) Microsoft Corporation -- Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64) -- ---------------------------------------------------------------- --> 测试数据:[a] if object_id('[a]') is not null drop table [a] go create table [a]([a] int) insert [a] select 1 union all select 2 union all select 3 --> 测试数据:[b] if object_id('[b]') is not null drop table [b] go create table [b]([b] int) insert [b] select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 --------------开始查询-------------------------- delete from b where not exists(select 1 from a where a=b.b) select * from b ----------------结果---------------------------- /* b ----------- 1 2 3(3 行受影响) */
delete from B表 b
where not exists
(select 1 from A表 a
where a.columnA=b.columnB)
-- Author :fredrickhu(小F,向高手学习)
-- Date :2012-07-17 13:58:50
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([a] int)
insert [a]
select 1 union all
select 2 union all
select 3
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([b] int)
insert [b]
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6
--------------开始查询--------------------------
delete from b where not exists(select 1 from a where a=b.b)
select * from b
----------------结果----------------------------
/* b
-----------
1
2
3(3 行受影响)
*/