a表
ID type
a 111
b 222
c fdsa
b表
id type
a 111
b 222
fda 555
a 33
b 111
---删除后,是以下结果,------------
b表
id type
fda 555
a 33
b 111
删除B表,(id,type) 列同时存在于a的记录
------解决方案--------------------
delete b from a where a.id=b.id and a.type=b.type
------解决方案--------------------
我来个不同的好了，大数据量一次更新的话我这个应该更好
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-03-14 17:29:45
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([ID] varchar(1),[type] varchar(4))
insert [a]
select 'a','111' union all
select 'b','222' union all
select 'c','fdsa'
--------------开始查询--------------------------
--> 测试数据：[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([id] varchar(3),[type] int)
insert [b]
select 'a',111 union all
select 'b',222 union all
select 'fda',555 union all
select 'a',33 union all
select 'b',111
--------------开始查询--------------------------
select * INTO #t FROM (
select * from [b]
except
select * from [a])a
TRUNCATE TABLE b
INSERT INTO b
SELECT * FROM #t
DROP TABLE #t
----------------结果----------------------------
/*
*/