级联更新。
要求:
1.表2字段Name更改的时候,可以更改到表1的Name字段.
表1 表2
------------------------------- ---------------------------------------
Name(text) BH(varchar) Name(text) MC(varchar)
2-20 100 2-12 Lily
2-20 50 2-12 Lily
2-20 10 2-100 Mondy
2-20 100 2-100 Mondy
2-20 50 2-20 Sandy
2-20 10 2-20 Sandy
2-12 100
2-12 50
2-12 10
2-12 100
2-12 50
2-12 10
2-100 100
2-100 50
2-100 10
2-100 100
2-100 50
2-100 10
结果:
表1 表2
------------------------------- ---------------------------------------
Name(text) BH(varchar) Name(text) MC(varchar)
2-20-1 100 2-12-1 Lily
2-20-1 50 2-12-2 Lily
2-20-1 10 2-100-1 Mondy
2-20-2 100 2-100-2 Mondy
2-20-2 50 2-20-1 Sandy
2-20-2 10 2-20-2 Sandy
2-12-1 100
2-12-1 50
2-12-1 10
2-12-2 100
2-12-2 50
2-12-2 10
2-100-1 100
2-100-1 50
2-100-1 10
2-100-2 100
2-100-2 50
2-100-2 10
说明:
1.表1同表2的Name字段虽然要求是文本形式,但内容却都是数字.
2.表1的BH字段数字都是象征性的,不一定全部都是100,50的重复,但几条相同Name字段之间,BH字段却都是通过内容重复来识别所属项的.
3.表1从属于表2,两者之间是从属关系.
请教.
问题点数：20、回复次数：3Top
1 楼chacall（宁夏）回复于 2006-07-04 09:48:25 得分 0
觉得乱麻烦的。。。。。Top
2 楼zjdyzwx（十一月猪）回复于 2006-07-04 16:38:14 得分 15
create table #
(name varchar(20) , bh varchar(20))
create table #1
(name varchar(20) , MC varchar(20))
truncate table #
insert into #
select '2-20', '100' union all
select '2-20', '50' union all
select '2-20', '10' union all
select '2-20', '100' union all
select '2-20', '50' union all
select '2-20', '10' union all
select '2-12', '100' union all
select '2-12', '50' union all
select '2-12', '10' union all
select '2-12','100' union all
select '2-12', '50' union all
select '2-12', '10' union all
select '2-100', '100' union all
select '2-100', '50' union all
select '2-100', '10' union all
select '2-100', '100' union all
select '2-100', '50' union all
select '2-100', '10'
select * into test111 from # where 1 = 1
insert into #1
select '2-12', 'Lily' union all
select '2-12', 'Lily' union all
select '2-100', 'Mondy' union all
select '2-100', 'Mondy' union all
select '2-20', 'Sandy' union all
select '2-20', 'Sandy'
select * into test11111 from #1 where 1 = 1
create trigger t_update
on test11111
for update
as
begin
update test111 set name =c.name from deleted a , test111 b , inserted c
where a.name = b.name
end
begin tran
update test11111 set name = '2-12-01' where mc = 'Lily'
select * from test111
rollback tran
Top
3 楼liaowei0627（）回复于 2006-07-04 18:04:32 得分 5
这两个表没有主键，也没有外键，数据库无法维护参照完整性，只能通过代码人工转换，用临时表转是个不错的方法。
如果有主外键，就可以在外键上定义级联更新，一劳永逸。
或者创建触发器，更新一个表，自动触发更新另一个表的update语句。Top
