有三张表.分别叫A,B,C,A和B是单独的表通过C关系
A表字段:AID,AName
B表字段:BID,Bword
C表字段:CID,AID,BID
我有一个字符串11;22;33;
我第一新insert的B表的时候.插入表后是
A表:
AID ANAME
21 test1
22 test2 B表:
BID Bword
1 11
2 22
3 33 C表:
CID AID BID
1 21 1
2 22 1
3 22 2
4 22 3 当我更新的时候我传入的字符串为22;44;
33和11没有了.
我希望变成
A表:
AID ANAME
21 test1
22 test2 B表:
BID Bword
1 11
2 22
4 44 C表:
CID AID BID
1 21 1
3 22 2
4 22 4 其中在B表中Bword中的11不能被删除,因为关系表里其它的数据引用他.
写一个更新的存储过程,如何写
A表字段:AID,AName
B表字段:BID,Bword
C表字段:CID,AID,BID
我有一个字符串11;22;33;
我第一新insert的B表的时候.插入表后是
A表:
AID ANAME
21 test1
22 test2 B表:
BID Bword
1 11
2 22
3 33 C表:
CID AID BID
1 21 1
2 22 1
3 22 2
4 22 3 当我更新的时候我传入的字符串为22;44;
33和11没有了.
我希望变成
A表:
AID ANAME
21 test1
22 test2 B表:
BID Bword
1 11
2 22
4 44 C表:
CID AID BID
1 21 1
3 22 2
4 22 4 其中在B表中Bword中的11不能被删除,因为关系表里其它的数据引用他.
写一个更新的存储过程,如何写
应该必须保证ANAME 和 Bword 唯一
AID int,
ANAME varchar(10)
)
goinsert A select
21, 'test1'
union all select
22, 'test2'
gocreate table B(
BID int IDENTITY,
Bword varchar(10)
)
go
insert B select
'11'
union all select
'22'
union all select
'33'
gocreate table C(
CID int IDENTITY,
AID int,
BID int
)
go
insert C select
21, 1
union all select
22, 1
union all select
22, 2
union all select
22, 3
gocreate proc pr_update
@search varchar(100)
,@update varchar(100)
as-- 拆分
declare @s table (
s varchar(10)
)
declare @i int
set @i =CHARINDEX(';',@search)
while @i >0
begin
insert @s values(LEFT(@search,@i-1))
set @search =STUFF(@search,1,@i,'')
set @i =CHARINDEX(';',@search)
end if @search <>''
insert @s values(@search)declare @u table (
u varchar(10)
)
set @i =CHARINDEX(';',@update)
while @i >0
begin
insert @u values(LEFT(@update,@i-1))
set @update =STUFF(@update,1,@i,'')
set @i =CHARINDEX(';',@update)
end if @update <>''
insert @u values(@update)
-- 查找
declare @aid intselect @aid=C.AID
from C
where exists (
select 1 from @s s,B
where B.Bword = s.s
and B.BID =C.bid
)
group by C.AID
having COUNT(1)=(select COUNT(1) from @s)
-- 删除C
delete C
from C
where AID=@aid
and not exists (
select 1 from B,@u u
where B.Bword = u.u
and B.BID =C.bid
)-- 删除B
delete B
from B
where exists (
select 1 from @s s
where B.Bword = s.s
)
and not exists (
select 1 from C
where B.BID =C.bid
)
-- 增加B
insert B(Bword )
select U from @u u
where not exists (
select 1 from B
where Bword = u.u
)
-- 增加C
insert C(AID,BID)
select @aid as aid,B.BID
from @u u,B
where Bword = u.u
and not exists (
select 1 from C
where C.BID = B.BID
) go
-- 调用
exec pr_update '11;22;33','22;44'
go
--显示结果
select * from A
select * from B
select * from C--结果
AID ANAME
----------- ----------
21 test1
22 test2(2 行受影响)BID Bword
----------- ----------
1 11
2 22
4 44(3 行受影响)CID AID BID
----------- ----------- -----------
1 21 1
5 22 4
3 22 2(3 行受影响)