SQL SERVER2000:有三个表,A,B,CA:
fdNo,fdSubNo,fdItemB:
fdNo,fdSubNo,fdNameC:
fdNum,fdSubNum,fdName现在想用b.fdName JOIN c.fdName,得到c.fdNum,c.fdSubNum来更新b.fdNo,fdSubNo,而b.fdNo,b.fdSubNo和a.fdNo,a.fdSubNo关联,因此在更新B的fdNo,fdSubNo同时也要更新C的fdNo,fdSubNo比如用数据形容就是如下:
A:
200101,01,''
200101,02,'item'
B:
200101,01,'z'
200101,02,'h'
C:
200601,01,'z'
200602,01,'h'那么更新后的
B:
200601,01,'z',
200602,01,'h'
A:
200601,01,''
200602,01,'item'就是这个样子,用SQL脚本可以实现吧,怎么实现呢,或者用级联更新实现,级联更新要如何设置呢?谢谢!
fdNo,fdSubNo,fdItemB:
fdNo,fdSubNo,fdNameC:
fdNum,fdSubNum,fdName现在想用b.fdName JOIN c.fdName,得到c.fdNum,c.fdSubNum来更新b.fdNo,fdSubNo,而b.fdNo,b.fdSubNo和a.fdNo,a.fdSubNo关联,因此在更新B的fdNo,fdSubNo同时也要更新C的fdNo,fdSubNo比如用数据形容就是如下:
A:
200101,01,''
200101,02,'item'
B:
200101,01,'z'
200101,02,'h'
C:
200601,01,'z'
200602,01,'h'那么更新后的
B:
200601,01,'z',
200602,01,'h'
A:
200601,01,''
200602,01,'item'就是这个样子,用SQL脚本可以实现吧,怎么实现呢,或者用级联更新实现,级联更新要如何设置呢?谢谢!
update a set fditem = fdname
from a inner join b on a.fdno = b.fdno and a.fdsubno = b.fdsubnoupdate a set fdno = fdnum, fdsubno = fdsubnum
from a inner join c on a.fditem = c.fdnameupdate b set fdno = fdnum, fdsubno = fdsubnum
from b inner join c on b.fdname = c.fdname
Create Table A(fdNo numeric(8),fdSubNo varchar(2),fdItem varchar(10) Null)
Create Table B(fdNo numeric(8),fdSubNo varchar(2),fdItem varchar(10) Null)
Create Table C(fdNo numeric(8),fdSubNo varchar(2),fdItem varchar(10) Null)
Insert into A
Select 200101,'01','' Union all
Select 200101,'02','item'
Insert into B
Select 200101,'01','z'Union all
Select 200101,'02','h'
Insert into C
Select 200601,'01','z'Union all
Select 200602,'01','h'
--------------------------------------------
Update A set fdno=C.fdno from A inner join B on a.fdno=b.fdno And A.fdsubno=B.fdsubno
inner join C on b.fditem=C.fditem
Update B set fdno=C.fdno from B inner join C on b.fditem=c.fditem
---------------------结果---------------------
select * from a
select * from b
select * from c