有两表:
表一(机构表):
Code, Name
420103010101 大龙滩组
420103010102 马家湾组
表二:(到户信息)
FanmilyCode, 姓名
420103010101001 张三1
420103010101002 张三2
420103010101003 张三3
420103010102001 张三4
420103010102002 张三5
420103010102002 张三6
420103010102004 张三7
420103010103001 张三8
420103010103002 张三9
我想把不在机构表中的 张三8,张三9 从表二中删除
表一(机构表):
Code, Name
420103010101 大龙滩组
420103010102 马家湾组
表二:(到户信息)
FanmilyCode, 姓名
420103010101001 张三1
420103010101002 张三2
420103010101003 张三3
420103010102001 张三4
420103010102002 张三5
420103010102002 张三6
420103010102004 张三7
420103010103001 张三8
420103010103002 张三9
我想把不在机构表中的 张三8,张三9 从表二中删除
DELETE A
FROM TB1 A LEFT JOIN TB2 B ON A.FanmilyCode = B.FanmilyCode
WHERE B.FanmilyCode IS NULL
delete a from 表二 a where exists(select 1 from 表一 where Code=a.FanmilyCode)楼主给的数据是不是有问题?
delete from tab where substring('FanmilyCode',1,12)>'420103010102'
from tb1
where left(FanmilyCode,12) not in (select code from tb1 )
-- Author: T.O.P
-- Create date: 20091127
-- Version: SQL SERVER 2000
-- =============================================
declare @TB1 table([Code] varchar(55),[Name] varchar(8))
insert @TB1
select 420103010101,'大龙滩组' union all
select 420103010102,'马家湾组'declare @TB2 table([FanmilyCode] varchar(55),[姓名] varchar(5))
insert @TB2
select 420103010101001,'张三1' union all
select 420103010101002,'张三2' union all
select 420103010101003,'张三3' union all
select 420103010102001,'张三4' union all
select 420103010102002,'张三5' union all
select 420103010102002,'张三6' union all
select 420103010102004,'张三7' union all
select 420103010103001,'张三8' union all
select 420103010103002,'张三9'DELETE A
FROM @TB2 A
WHERE NOT EXISTS(SELECT 1 FROM @TB1 WHERE A.FanmilyCode LIKE Code+'%')SELECT * FROM @TB2
--测试结果:
/*
FanmilyCode 姓名
------------------------------------------------------- -----
420103010101001 张三1
420103010101002 张三2
420103010101003 张三3
420103010102001 张三4
420103010102002 张三5
420103010102002 张三6
420103010102004 张三7(所影响的行数为 7 行)
*/
tb2
from
tb2
where
not exists (select 1 from tb1 where code=left(tb1.FanmilyCode,12) )
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-11-27 18:36:49
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb1]
if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([Code] bigint,[Name] varchar(8))
insert [tb1]
select 420103010101,'大龙滩组' union all
select 420103010102,'马家湾组'
--> 测试数据:[tb2]
if object_id('[tb2]') is not null drop table [tb2]
go
create table [tb2]([FanmilyCode] bigint,[姓名] varchar(5))
insert [tb2]
select 420103010101001,'张三1' union all
select 420103010101002,'张三2' union all
select 420103010101003,'张三3' union all
select 420103010102001,'张三4' union all
select 420103010102002,'张三5' union all
select 420103010102002,'张三6' union all
select 420103010102004,'张三7' union all
select 420103010103001,'张三8' union all
select 420103010103002,'张三9'
--------------开始查询--------------------------
delete
tb2
from
tb2
where
not exists (select 1 from tb1 where code=left(tb2.FanmilyCode,12) )
select * from [tb2]
----------------结果----------------------------
/* FanmilyCode 姓名
-------------------- -----
420103010101001 张三1
420103010101002 张三2
420103010101003 张三3
420103010102001 张三4
420103010102002 张三5
420103010102002 张三6
420103010102004 张三7(7 行受影响)
*/