sql server中怎么写联接更新?

[accpedu]

sql server中怎么写联接更新?

就是同时（在一条语句中）更新多个表！

[caixia615]

触发器

[accpedu]

不是有个联接更新的吗？有必要用触发器吗？

[accpedu]

高手快来啊

[中国风]

create table a1(produce varchar(10) primary key)
insert into a1 select 'A'
insert into a1 select 'B'
insert into a1 select 'C'
insert into a1 select 'D'
insert into a1 select 'E'

create table a2(produce varchar(10),val int)
insert into a2 select 'A',1
insert into a2 select 'B',2
insert into a2 select 'C',3
insert into a2 select 'D',4
insert into a2 select 'E',5

alter table a2 add constraint a2_con foreign key(produce)references a1(produce) on delete cascade


delete from a1 where produce='a'
select * from a1
select * from a2

drop table a1,a2

[中国风]

create table a1(produce varchar(10) primary key)
insert into a1 select 'A'
insert into a1 select 'B'
insert into a1 select 'C'
insert into a1 select 'D'
insert into a1 select 'E'

create table a2(produce varchar(10) not null
foreign key references a1(produce) on delete cascade--建表时就这样定义
,val int)
insert into a2 select 'A',1
insert into a2 select 'B',2
insert into a2 select 'C',3
insert into a2 select 'D',4
insert into a2 select 'E',5

--alter table a2 add constraint a2_con foreign key(produce)references a1(produce) on udpate cascade--这里为添加级联更新


delete from a1 where produce='a'
select * from a1
select * from a2

drop table a1,a2
楼主通过企业管理器设置不是更简单，只需要在表与表之间选择级联更新和删除就行了

[中国风]

create table a1(produce varchar(10) primary key)
insert into a1 select 'A'
insert into a1 select 'B'
insert into a1 select 'C'
insert into a1 select 'D'
insert into a1 select 'E'

create table a2(produce varchar(10) not null
foreign key references a1(produce) on delete cascade
on update cascade--建表时就这样定义(更新与删除)
,val int)
insert into a2 select 'A',1
insert into a2 select 'B',2
insert into a2 select 'C',3
insert into a2 select 'D',4
insert into a2 select 'E',5

测试更新:
update a1
set produce='A1'
where produce='A'
select * from a2
A1 1
B 2
C 3
D 4
E 5

（所影响的行数为 5 行）

测试删除:
delete from a1 where produce='a1'
select * from a2
produce val
---------- -----------
B 2
C 3
D 4
E 5

（所影响的行数为 4 行）
drop table a1,a2