我想删除 表1.type1='1' and 表1.info1='2' 表2.type2='1' and 表1.info2='2' 表3.type3='1' and 表1.info3='2' 这些记录
以下,我写了个依赖主健约束创建的分区视图,这种做法局限性很大. 首先,限制了 t1,t2,t3的type的值的分布. 当然, 在实际应用中,几个表的type可以有重叠区域,但是必须另加主健,保证几个表的主健值不能重叠. 比如t1,t2,t3中各有id列. 以id为主健,做视图分区.不排除手误.create table t1(type1 int primary key check(type1 between 1 and 10), info1 int) insert t1 select 1,2 union select 3,2 union select 2,1 create table t2(type2 int primary key check(type2 between 11 and 20), info2 int) insert t2 select 11,1 union select 15,2 union select 12,1 create table t3(type3 int primary key check(type3 between 21 and 100), info3 int) insert t3 select 31,1 union select 33,2 union select 23,1 go create view t as select type1,info1 from t1 union all select type2,info2 from t2 union all select type3,info3 from t3 go select * from t delete from t where (type1=1 and info1=2) or (type1=1 and info1=2) or (type1=1 and info1=2) /* 或用以下两种写法的任一种 delete from t where type1=1 and info1=2 delete a from t a inner join (select type1=1,info1=2 union select type1=1,info1=2 union select type1=1,info1=2) b on a.type1=b.type1 and a.info1=b.info1*/
go select * from t drop table t1,t2,t3 drop view t go 如果不依赖于主健分区,那么还是要借助触发器.只不过建视图时,给每个表另个来源识别的虚列.create view t as select *,frm=1 from t1 union select *,frm=2 from t2 union select *,frm=3 from t3--进行删除操作 delete from t where (type1=1 and info1=2) or (type1=1 and info1=2) or (type1=1 and info1=2) and frm in(1,2,3) --只删除t1中的,那么in中就只出现1 /* 或用以下两种写法的任一种 delete from t where type1=1 and info1=2 and frm in(1,2,3) delete a from t a inner join (select type1=1,info1=2,frm=1 union select type1=1,info1=2,frm=2 union select type1=1,info1=2,frm=3) b on a.type1=b.type1 and a.info1=b.info1 and a.frm=b.frm*/--然后,给t创建 instead of delete触发器,在触发器中完成操作. create trigger tr on t instead of delete ...--略去
使用 left join 和right join
create table t1(type1 int primary key check(type1 between 1 and 10), info1 int) insert t1 select 1,2 union select 3,2 union select 2,1 create table t2(type2 int primary key check(type2 between 11 and 20), info2 int) insert t2 select 11,1 union select 15,2 union select 12,1 create table t3(type3 int primary key check(type3 between 21 and 100), info3 int) insert t3 select 31,1 union select 33,2 union select 23,1 go create view t as select type1,info1 from t1 union all select type2,info2 from t2 union all select type3,info3 from t3 go select * from t delete from t where (type1=1 and info1=2) or (type1=1 and info1=2) or (type1=1 and info1=2) /* 或用以下两种写法的任一种 delete from t where type1=1 and info1=2 delete a from t a inner join (select type1=1,info1=2 union select type1=1,info1=2 union select type1=1,info1=2) b on a.type1=b.type1 and a.info1=b.info1*/
go select * from t drop table t1,t2,t3 drop view t go
即是要union 还是 join
不明白你这个视图将会是怎样的结构. 因为你所说的删数据,我也不知道是指删什么.
表1.type1='1' and 表1.info1='2'
表2.type2='1' and 表1.info2='2'
表3.type3='1' and 表1.info3='2'
这些记录
insert t1 select 1,2 union select 3,2 union select 2,1
create table t2(type2 int primary key check(type2 between 11 and 20), info2 int)
insert t2 select 11,1 union select 15,2 union select 12,1
create table t3(type3 int primary key check(type3 between 21 and 100), info3 int)
insert t3 select 31,1 union select 33,2 union select 23,1
go
create view t
as
select type1,info1 from t1
union all
select type2,info2 from t2
union all
select type3,info3 from t3
go
select * from t
delete from t where (type1=1 and info1=2) or (type1=1 and info1=2) or (type1=1 and info1=2)
/*
或用以下两种写法的任一种
delete from t where type1=1 and info1=2
delete a from t a inner join
(select type1=1,info1=2 union select type1=1,info1=2 union select type1=1,info1=2) b
on a.type1=b.type1 and a.info1=b.info1*/
go
select * from t
drop table t1,t2,t3
drop view t
go
如果不依赖于主健分区,那么还是要借助触发器.只不过建视图时,给每个表另个来源识别的虚列.create view t as
select *,frm=1 from t1
union
select *,frm=2 from t2
union
select *,frm=3 from t3--进行删除操作
delete from t where (type1=1 and info1=2) or (type1=1 and info1=2) or (type1=1 and info1=2) and frm in(1,2,3) --只删除t1中的,那么in中就只出现1
/*
或用以下两种写法的任一种
delete from t where type1=1 and info1=2 and frm in(1,2,3)
delete a from t a inner join
(select type1=1,info1=2,frm=1 union select type1=1,info1=2,frm=2 union select type1=1,info1=2,frm=3) b
on a.type1=b.type1 and a.info1=b.info1 and a.frm=b.frm*/--然后,给t创建 instead of delete触发器,在触发器中完成操作.
create trigger tr
on t
instead of delete
...--略去
insert t1 select 1,2 union select 3,2 union select 2,1
create table t2(type2 int primary key check(type2 between 11 and 20), info2 int)
insert t2 select 11,1 union select 15,2 union select 12,1
create table t3(type3 int primary key check(type3 between 21 and 100), info3 int)
insert t3 select 31,1 union select 33,2 union select 23,1
go
create view t
as
select type1,info1 from t1
union all
select type2,info2 from t2
union all
select type3,info3 from t3
go
select * from t
delete from t where (type1=1 and info1=2) or (type1=1 and info1=2) or (type1=1 and info1=2)
/*
或用以下两种写法的任一种
delete from t where type1=1 and info1=2
delete a from t a inner join
(select type1=1,info1=2 union select type1=1,info1=2 union select type1=1,info1=2) b
on a.type1=b.type1 and a.info1=b.info1*/
go
select * from t
drop table t1,t2,t3
drop view t
go
即是要union 还是 join
不明白你这个视图将会是怎样的结构. 因为你所说的删数据,我也不知道是指删什么.