关于删除问题????

oyyd1984 2008-03-21 04:36:33
表1
aa bb cc
AA 1 afdf
AA 2 dfa
AA 3 efafe
BB 2 dfa
CC 3 vagd
CC 4 rgdfad


表2
aa bb
AA 2
CC 4

aa,bb为主键,当表2出现时,删除表1的相应行,操作后表1为
aa bb cc
AA 1 afdf
AA 3 efafe
BB 2 dfa
CC 3 vagd

请问代码如何?谢谢各位!
...全文
51 点赞 收藏 10
写回复
10 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
oyyd1984 2008-03-21
[Quote=引用 7 楼 liangCK 的回复:]
SQL codecreate table tb1(aa varchar(10),bb int,cc varchar(10))
insert tb1 select 'AA' ,1, 'afdf'
insert tb1 select 'AA' ,2, 'dfa'
insert tb1 select 'AA' ,3, 'efafe'
insert tb1 select 'BB' ,2, 'dfa'
insert tb1 select 'CC' ,3, 'vagd'
insert tb1 select 'CC' ,4, 'rgdfad'

create table tb2(aa varchar(10),bb int)
insert tb2 select 'AA', 2
insert tb2 select 'CC', 4
go
delete a
from t…
[/Quote]

delete 后面还有一个a,上面的代码没有啊,现在可以了,谢谢啊!我已经给分拉!

回复
kk19840210 2008-03-21
~~~~~~~~~~~~~[Quote=引用 5 楼 oyyd1984 的回复:]
引用 1 楼 liangCK 的回复:
delete
from t1 a
where exists(select * from tb2 where aa=a.aa and bb=a.bb)


不行啊,出现
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'a'. 啊!!! 什么回事啊?
[/Quote]
回复
kk19840210 2008-03-21


create table #tab1(aa varchar(2),bb int,cc varchar(10))
insert into #tab1 values('AA',1,'afdf')
insert into #tab1 values('AA',2,'dfa')
insert into #tab1 values('AA',3,'efafe')
insert into #tab1 values('BB',2,'dfa')
insert into #tab1 values('CC',3,'vagd')
insert into #tab1 values('CC',4,'rgdfad')

create table #tab2(aa varchar(2),bb int)
INSERT INTO #TAB2 VALUES('AA',2)
INSERT INTO #TAB2 VALUES('CC',4)

delete #tab1 from #tab1 a where exists (select 1 from #tab2 where aa=a.aa and bb=a.bb)

aa bb cc
---- ----------- ----------
AA 1 afdf
AA 3 efafe
BB 2 dfa
CC 3 vagd

(4 行受影响)
回复
liangCK 2008-03-21
create table tb1(aa varchar(10),bb int,cc varchar(10))
insert tb1 select 'AA' ,1, 'afdf'
insert tb1 select 'AA' ,2, 'dfa'
insert tb1 select 'AA' ,3, 'efafe'
insert tb1 select 'BB' ,2, 'dfa'
insert tb1 select 'CC' ,3, 'vagd'
insert tb1 select 'CC' ,4, 'rgdfad'

create table tb2(aa varchar(10),bb int)
insert tb2 select 'AA', 2
insert tb2 select 'CC', 4
go
delete a
from tb1 a
where exists(select * from tb2 where aa=a.aa and bb=a.bb)

select * from tb1

drop table tb1,tb2

/*
aa bb cc
---------- ----------- ----------
AA 1 afdf
AA 3 efafe
BB 2 dfa
CC 3 vagd

(4 行受影响)
*/
回复
liangCK 2008-03-21
[Quote=引用 4 楼 kk19840210 的回复:]
再复制一下

这段代码我已经保存起来了太经典了
delete
from t1 a
where exists(select * from tb2 where aa=a.aa and bb=a.bb)
[/Quote]

汗哦..我可是经常写代码的好不好..
回复
oyyd1984 2008-03-21
[Quote=引用 1 楼 liangCK 的回复:]
delete
from t1 a
where exists(select * from tb2 where aa=a.aa and bb=a.bb)
[/Quote]

不行啊,出现
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'a'. 啊!!! 什么回事啊?
回复
kk19840210 2008-03-21
再复制一下

这段代码我已经保存起来了太经典了
delete
from t1 a
where exists(select * from tb2 where aa=a.aa and bb=a.bb)
回复
liangCK 2008-03-21
[Quote=引用 2 楼 kk19840210 的回复:]
不经常出现小梁自己写的代码
当然要复制下了

delete
from t1 a
where exists(select * from tb2 where aa=a.aa and bb=a.bb)
[/Quote]

哈哈..你这话太不够意思了..
回复
kk19840210 2008-03-21
不经常出现小梁自己写的代码
当然要复制下了

delete
from t1 a
where exists(select * from tb2 where aa=a.aa and bb=a.bb)
回复
liangCK 2008-03-21
delete
from t1 a
where exists(select * from tb2 where aa=a.aa and bb=a.bb)
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-03-21 04:36
社区公告
暂无公告