求一简单SQL

ttyp 2003-10-21 11:58:33

表T1
A B C
1 2 3
2 1 1
3 2 1
4 1 1
表T2
A B C
1 2 3
2 1 1
现在要删除T1中，有和T2一样的记录
结果
T1
3 2 1
4 1 1

...全文

30 26 打赏收藏转发到动态举报

写回复

用AI写文章

26 条回复

切换为时间正序

请发表友善的回复…

发表回复

pengdali 2003-10-25

打赏
举报

楼主写错了

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 #t1

drop table #t1
drop table #t2

lijinqiang 2003-10-25

打赏
举报

delete 表T1 WHERE 第一个字段 NOT IN （SELECT 第一个字段 FROM 表2）

银狐被占用 2003-10-25

打赏
举报

delete t1 join t2 on t1.a=t2.a and t1.b=t2.b and t1.c=t2.c

LoveSQL 2003-10-25

打赏
举报

delete T1 from t1,t2
where t1.a=t2.a and t1.b=t2.b and t1.c=t2.c

welyngj 2003-10-24

打赏
举报

delete t1 where exists(select 1 from t2 where a=t1.a and b=t1.b and c=t1.c)

ttyp 2003-10-24

打赏
举报

----------------
只能是查询，不能够delete

chenzhuo 2003-10-24

打赏
举报

delete t1 where exists(select 1 from t2 where a=t1.a and b=t1.b and c=t1.c)

ttyp 2003-10-24

打赏
举报

寒，有没测试过啊~~~

fjw2002 2003-10-24

打赏
举报

delete t1 where exists(select 1 from t2 where a=t1.a and b=t1.b and c=t1.c)

lvltt 2003-10-24

打赏
举报

同意楼上

sai1691 2003-10-22

打赏
举报

delete table t1 where exists(select 1 from t2 where a=t1.a and b=t1.b and c=t1.c)

gmlxf 2003-10-21

打赏
举报

以下应该可以的：
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 #t3

drop table #t1
drop table #t2
drop table #t3

gmlxf 2003-10-21

打赏
举报

我的错的。。

gmlxf 2003-10-21

打赏
举报

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

ttyp 2003-10-21

打赏
举报

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 #t1

drop table #t1
drop table #t2

--好象有点问题

yujohny 2003-10-21