select * from t1 where a not in (select a from t2)
delete t1 where a in (select a from t2)
delete t1 where exists(select 1 from t2 where a=t1.a and b=t1.b and c=t1.c)
delete T1 where exists(select 1 from T2 where a=T1.a and b=T1.b and c=T1.c)
create table #t1 (a int,b int) insert into #t1 values(1,1) insert into #t1 values(2,1) insert into #t1 values(3,1) insert into #t1 values(4,1) create table #t2 (a int,b int) insert into #t2 values(1,1) insert into #t2 values(2,1)delete #t1 where exists(select 1 from #t2 y where a = y.a and b=y.b) select * from #t1drop table #t1 drop table #t2--好象有点问题
create table #t1 (a int,b int) insert into #t1 values(1,1) insert into #t1 values(2,1) insert into #t1 values(3,1) insert into #t1 values(4,1) create table #t2 (a int,b int) insert into #t2 values(1,1) insert into #t2 values(2,1)delete #t1 where a in(select y.a from #t2 y) and b in(select y.b from #t2 y)drop table #t1 drop table #t2
以下应该可以的: create table #t1 (a int,b int) insert into #t1 values(1,1) insert into #t1 values(2,1) insert into #t1 values(2,2) insert into #t1 values(1,2) insert into #t1 values(4,1) create table #t2 (a int,b int) insert into #t2 values(1,1) insert into #t2 values(2,2)select * from #t1 select * from #t2 select identity(int,1,1) as id,* into #t3 from #t1 delete from #t3 where id in ( select id from #t3 y where exists(select 1 from #t2 where a = y.a and b=y.b) )select a,b from #t3drop table #t1 drop table #t2 drop table #t3
insert into #temp()select * from t1 union select * from t2delete t1insert into t1 select * from #temp
select * from t1 a where exists(select 1 from t2 where a.a=a and a.b=b and a.c=c)
select * from t1 a where exists(select 1 from t2 where a.a=a and a.b=b and a.c=c) ---------------- 只能是查询,不能够delete
其实你这个就是相当与单表删除重复记录的问题。你参考以下,是蚂蚁写的: 你的是没有id的类型:1、如果有ID字段,就是具有唯一性的字段delect table where id not in ( select max(id) from table group by col1,col2,col3... ) group by 子句后跟的字段就是你用来判断重复的条件,如只有col1,那么只要col1字段内容相同即表示记录相同。 2,如果是判断所有字段也可以这样 select * into #aa from table group by id1,id2,.... delete table insert into table select * from #aa 3,没有ID的情况select identity(int,1,1) as id,* into #temp from tabel delect # where id not in ( select max(id) from # group by col1,col2,col3...) delect table inset into table(...) select ..... from #temp col1+','+col2+','...col5 联合主键 select * from table where col1+','+col2+','...col5 in ( select max(col1+','+col2+','...col5) from table where having count(*)>1 group by col1,col2,col3,col4 ) group by 子句后跟的字段就是你用来判断重复的条件,如只有col1,那么只要col1字段内容相同即表示记录相同。2, select identity(int,1,1) as id,* into #temp from tabel select * from #temp where id in ( select max(id) from #emp where having count(*)>1 group by col1,col2,col3...)
delete table t1 where exists(select 1 from t2 where a=t1.a and b=t1.b and c=t1.c)
---------------- 只能是查询,不能够delete
delete t1 where exists(select 1 from t2 where a=t1.a and b=t1.b and c=t1.c)
delete t1 where exists(select 1 from t2 where a=t1.a and b=t1.b and c=t1.c)
delete t1 where exists(select 1 from t2 where a=t1.a and b=t1.b and c=t1.c)
delete 表T1 WHERE 第一个字段 NOT IN (SELECT 第一个字段 FROM 表2)
楼主写错了create table #t1 (a int,b int) insert into #t1 values(1,1) insert into #t1 values(2,1) insert into #t1 values(3,1) insert into #t1 values(4,1) create table #t2 (a int,b int) insert into #t2 values(1,1) insert into #t2 values(2,1)delete #t1 where exists(select 1 from #t2 y where a = #t1 .a and b=#t1.b) select * from #t1drop table #t1 drop table #t2
delete t1 join t2 on t1.a=t2.a and t1.b=t2.b and t1.c=t2.c
delete T1 from t1,t2 where t1.a=t2.a and t1.b=t2.b and t1.c=t2.c
insert into #t1 values(1,1)
insert into #t1 values(2,1)
insert into #t1 values(3,1)
insert into #t1 values(4,1)
create table #t2 (a int,b int)
insert into #t2 values(1,1)
insert into #t2 values(2,1)delete #t1 where exists(select 1 from #t2 y where a = y.a and b=y.b)
select * from #t1drop table #t1
drop table #t2--好象有点问题
insert into #t1 values(1,1)
insert into #t1 values(2,1)
insert into #t1 values(3,1)
insert into #t1 values(4,1)
create table #t2 (a int,b int)
insert into #t2 values(1,1)
insert into #t2 values(2,1)delete #t1 where a in(select y.a from #t2 y) and b in(select y.b from #t2 y)drop table #t1
drop table #t2
create table #t1 (a int,b int)
insert into #t1 values(1,1)
insert into #t1 values(2,1)
insert into #t1 values(2,2)
insert into #t1 values(1,2)
insert into #t1 values(4,1)
create table #t2 (a int,b int)
insert into #t2 values(1,1)
insert into #t2 values(2,2)select * from #t1
select * from #t2
select identity(int,1,1) as id,* into #t3 from #t1
delete from #t3 where id in
(
select id from #t3 y where exists(select 1 from #t2 where a = y.a and b=y.b)
)select a,b from #t3drop table #t1
drop table #t2
drop table #t3
union
select * from t2delete t1insert into t1
select * from #temp
----------------
只能是查询,不能够delete
你的是没有id的类型:1、如果有ID字段,就是具有唯一性的字段delect table where id not in ( select max(id) from table group by col1,col2,col3...
)
group by 子句后跟的字段就是你用来判断重复的条件,如只有col1,那么只要col1字段内容相同即表示记录相同。 2,如果是判断所有字段也可以这样
select * into #aa from table group by id1,id2,....
delete table
insert into table
select * from #aa 3,没有ID的情况select identity(int,1,1) as id,* into #temp from tabel
delect # where id not in (
select max(id) from # group by col1,col2,col3...)
delect table
inset into table(...)
select ..... from #temp
col1+','+col2+','...col5 联合主键
select * from table where col1+','+col2+','...col5 in ( select max(col1+','+col2+','...col5) from table
where having count(*)>1
group by col1,col2,col3,col4
)
group by 子句后跟的字段就是你用来判断重复的条件,如只有col1,那么只要col1字段内容相同即表示记录相同。2,
select identity(int,1,1) as id,* into #temp from tabel
select * from #temp where id in (
select max(id) from #emp where having count(*)>1 group by col1,col2,col3...)
只能是查询,不能够delete
insert into #t1 values(1,1)
insert into #t1 values(2,1)
insert into #t1 values(3,1)
insert into #t1 values(4,1)
create table #t2 (a int,b int)
insert into #t2 values(1,1)
insert into #t2 values(2,1)delete #t1 where exists(select 1 from #t2 y where a = #t1 .a and b=#t1.b)
select * from #t1drop table #t1
drop table #t2
where t1.a=t2.a and t1.b=t2.b and t1.c=t2.c