一个简单的删除语句

[mailto520]

表A
id operatedate abPlan
1 2008-12-1 050020
2 2008-10-2 050200
3 2008-10-3 050201

表B
id abPlan
1 050020
2 050201

删除后的表A为：
id operatedate abPlan
1 2008-12-1 050020
3 2008-10-3 050201

[liangCK]

DELETE A
FROM 表A AS A
WHERE NOT EXISTS(
          SELECT *
          FROM 表B
          WHERE abPlan=A.abPlan
      )

[liangCK]

---------------------------------
--  Author: liangCK 小梁
---------------------------------
 
--> 生成测试数据: @表A
DECLARE @表A TABLE (id INT,operatedate DATETIME,abPlan VARCHAR(6))
INSERT INTO @表A
SELECT 1,'2008-12-1','050020' UNION ALL
SELECT 2,'2008-10-2','050200' UNION ALL
SELECT 3,'2008-10-3','050201'
 
--> 生成测试数据: @表B
DECLARE @表B TABLE (id INT,abPlan VARCHAR(6))
INSERT INTO @表B
SELECT 1,'050020' UNION ALL
SELECT 2,'050201'

--SQL查询如下:

DELETE A
FROM @表A AS A
WHERE NOT EXISTS(
          SELECT *
          FROM @表B
          WHERE abPlan=A.abPlan
      )
      
SELECT *
FROM @表A

/*
id          operatedate             abPlan
----------- ----------------------- ------
1           2008-12-01 00:00:00.000 050020
3           2008-10-03 00:00:00.000 050201

(2 行受影响)

*/

[you_tube]

DELETE 
FROM 表A 
WHERE abPlan not  in  (
          SELECT abPlan
          FROM 表B)

[百年树人]

delete from A where abPlan not in(select abPlan from B)

[zhuwen9]

create table 表A
(
	id int identity(1,1) primary key,
	operatedate varchar(20),
	abPlan varchar(10)
	
)
go
create table 表B
(
	id int identity(1,1) primary key,
	abPlan varchar(10)
	
)
go 
insert into 表A select '2008-12-1','050020' 
          union select '2008-10-2','050200'
          union select '2008-10-3','050201'
go
insert into 表B select '050020' 
          union select '050201'

select * from 表A
select * from 表B

delete from 表A  where abPlan not in (select abPlan from 表B)

[you_tube]

[Quote=引用 1 楼 liangCK 的回复:]
SQL codeDELETEAFROM表AASAWHERENOTEXISTS(SELECT*FROM表BWHEREabPlan=A.abPlan
)
[/Quote]
这样效率还是最高的

[Andy__Huang]

1楼很正确!

[Eagle_ice]

[Quote=引用 1 楼 liangCK 的回复:]
SQL codeDELETE A
FROM 表A AS A
WHERE NOT EXISTS(
SELECT *
FROM 表B
WHERE abPlan=A.abPlan
)
[/Quote]
支持...

[lihan6415151528]

delete from A where abPlan not in(select abPlan from B)