首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 网站开发 > asp.net >

[]求一多表同时修改SQL语句

2012-01-02 
[求助]求一多表同时修改SQL语句?两表结构如下:A字段:AID,Name,FullName,BID(主键为AID,外键BID)B字段:BID,

[求助]求一多表同时修改SQL语句?
两表结构如下:
A
字段:AID,Name,FullName,BID(主键为AID,外键BID)
B
字段:BID,Name,FullName(主键为BID)
问题:
现在需要在更新A表Name,FullName字段内容的同时,也更新B表Name,FullName内容,
使当A.BID=B.BID时A.Name=B.Name,A.FullName=B.FullName???
1.请以一句SQL语句实现之,谢谢,初学!

2.删除的语句顺便也给写出来,谢谢!

[解决办法]
沙发先坐了再看~~~
[解决办法]
触发器

[解决办法]
update B set B.Name=A.Name, B.FullName=A.FullName where A.BID=B.BID
楼主是不是这个意思啊~~`
看的不是很明白
关注中,楼主说清楚点~~
[解决办法]
设置A表的外键BID的级联属性为级联更新和级联删除,就可以不用写如何代码,在删除或更新主表的时候,子表会自动更新和删除
[解决办法]
貌似偶的理解出问题了~~继续关注下~~
[解决办法]
A
字段:AID,Name,FullName,BID(主键为AID,外键BID)
B
字段:BID,Name,FullName(主键为BID)

sql语句试试这个

update A,B
SET A.Name = '1 ' AND A.FullName= '2 ' AND B.Name = '1 ' AND B.FullName= '2 '
WHERE A.BID=B.BID AND AID=1


[解决办法]
update A,B
SET A.Name = '1 ' AND A.FullName= '2 ' AND B.Name = '1 ' AND B.FullName= '2 '
WHERE A.BID=B.BID AND A.AID=1
[解决办法]
--根据A表的更新对B表触发更新
create or replace tirgger to_b_update()
after update on A
for each row
begin
update b set Name=(:new.Name),FullName=(:new,FullName) where BID=(:new.BID);
end

--根据A表的删除对B表触发删除
create or replace tirgger to_b_del()
after delete on A
for each row
begin
delete from b where BID=(:old.BID);
end
[解决办法]
A
字段:AID,Name,FullName,BID(主键为AID,外键BID)
B
字段:BID,Name,FullName(主键为BID)

---------------------------------------
試試:下面的SQL (連在一起的)
Update A set Name = 'XXX ',FullName = 'YYYY ' where AID = 'ZZZ ';
Update B set Name = (select name from a where AID = 'ZZZ ' ),
FullName =(select Fullname from a where AID = 'ZZZ ' )
where BID = ( select AID where AID = 'ZZZ ' )

[解决办法]
create or replace trigger to_b_del()
after update on A
for each row
begin
update b set Name=(:new.Name),FullName=(:new,FullName) where BID=(:new.BID);
end

create or replace trigger to_b_update()
after delete on A
for each row
begin
delete from b where BID=(:old.BID);
end

热点排行