如何删除三表关联数据？

[DTWUJP]

如A表
id name date flag
01 a 2009-01-01 4
02 b 2009-05-01 1
03 c 2009-05-01 4
04 d 2009-07-01 1
......
b表
id xmid xmname fair
01 a01 aaaa 300
01 a02 bbbbb 40
01 a03 ccccc 10
02 a07 dddd 0
02 a06 eeee 0
02 a14 ffff 30
03 a25 g 56
04 a25 g 23
.........
C表
xmid xm price count
a01 1 6 2
a01 2 5 6
a01 3 3 54
a02 1 4 10

.....

a表通过id和B表关联，a表和b表是一对多关系,b表和c表通过xmid关联，b表和c表是多对多关系,
请问，如何将a表中日期在‘2009-06-01’以前和flag=4的b表和c表数据删除，但保留a表中的数据?

[guguda2008]

级联删除或者存储过程

[DTWUJP]

谢谢楼上，表中没有外键，能给个具体语句吗？

[xman_78tom]

delete C where xmid in
(select xmid from B where id in (select id from A where [date]<'2009-06-01' and flag=4))

delete B where id in (select id from A where [date]<'2009-06-01' and flag=4)

[SQL77]

建立外键关联起来呀,用级联方便

要不写个触发器

[ws_hgo]

create table #A
(
  id varchar(10),
  name varchar(20),
  date datetime,
  flag int
)
insert into #A select '01','a','2009-01-01',4
union all select '02','b','2009-05-01',1
union all select '03','c','2009-05-01',4
union all select '04','d','2009-07-01',1
create table #B1
(
  id varchar(10),
  xmid varchar(10),
  xmname nvarchar(20),
  fair int 
)
insert into #B1 select '01','a01','aaaa',300
union all select '01','a02','bbbbb',40
union all select '01','a03','ccccc',10
union all select '02','a07','dddd',0
union all select '02','a06','eeee',0
union all select '02','a14','ffff',30
union all select '03','a25','g',56
union all select '04','a25','g',23
create table #C1
(
  xmid varchar(10),
  xm int,
  price int,
  count int
)
insert into #C1 select 'a01',1,6,2
union all select 'a01',2,5,6
union all select 'a01',3,3,54
union all select 'a02',1,4,10
/*
表通过id和B表关联，a表和b表是一对多关系,b表和c表通过xmid关联，b表和c表是多对多关系, 
请问，如何将a表中日期在‘2009-06-01’以前和flag=4的b表和c表数据删除，但保留a表中的数据?
*/

--删除C表的
delete from #C1 where xmid in
(
 select xmid from #B1 where id in (select id from #A where date<'2009-06-01' and flag=4)
)
--删除B表
delete from #B1 where id in (select id from #A where date<'2009-06-01' and flag=4)

[feixianxxx]

......

[lidanzi]

学习来的

[feixianxxx]

create table #A
(
  id varchar(10),
  name varchar(20),
  date datetime,
  flag int
)
insert into #A select '01','a','2009-01-01',4
union all select '02','b','2009-05-01',1
union all select '03','c','2009-05-01',4
union all select '04','d','2009-07-01',1
create table #B1
(
  id varchar(10),
  xmid varchar(10),
  xmname nvarchar(20),
  fair int 
)
insert into #B1 select '01','a01','aaaa',300
union all select '01','a02','bbbbb',40
union all select '01','a03','ccccc',10
union all select '02','a07','dddd',0
union all select '02','a06','eeee',0
union all select '02','a14','ffff',30
union all select '03','a25','g',56
union all select '04','a25','g',23
create table #C1
(
  xmid varchar(10),
  xm int,
  price int,
  count int
)
insert into #C1 select 'a01',1,6,2
union all select 'a01',2,5,6
union all select 'a01',3,3,54
union all select 'a02',1,4,10
GO
CREATE PROC DEL_A 
AS
BEGIN
--先要删除#c1 
delete #C1 
from #a a join #b1 b on a.id=b.id 
JOIN #C1 C ON B.xmid=C.xmid
where [date]<'2009-06-01' and flag=4
--然后删除关联的#B1
delete #b1 
from #a a join #b1 b on a.id=b.id 
where [date]<'2009-06-01' and flag=4
END 
--
exec dbo.DEL_A 
--检测--
SELECT * FROM #B1
SELECT * FROM #C1
/*
id         xmid       xmname               fair
---------- ---------- -------------------- -----------
02         a07        dddd                 0
02         a06        eeee                 0
02         a14        ffff                 30
04         a25        g                    23

(4 行受影响)

xmid       xm          price       count
---------- ----------- ----------- -----------
*/

果果数据用下