update a set a.tagno=b.tango from catalog a,catalog b where a.parent_id=b.id
update catalog set tagno=b.tagno from catalog,catalog b where b.id=catalog.parent_id
update a set a.tagno=b.tagno from catalog a,catalog b where a.parent_id=b.id
update catalog set tagno=(select tagno from catalog b where b.id=catalog.parent_id) where parent_id in (select parent_id from catalog)
--创建一个函数,得到指定结点的编码累计:--自定义函数--获取编码累计 create function f_getmergid(@id int) returns varchar(8000) as begin declare @re varchar(8000),@pid int --为了数字排序正常,需要统一编码宽度 declare @idlen int,@idheader varchar(20) select @idlen=max(len(id)) ,@idheader=space(@idlen) from catalog --得到编码累计 set @re=right(@idheader+cast(@id as varchar),@idlen) select @pid=parent_id from catalog where id=@id while @@rowcount>0 select @re=right(@idheader+cast(@pid as varchar),@idlen)+','+@re ,@pid=parent_id from catalog where id=@pid return(@re) end go--调用这个函数进行更新处理update catalog set TAGNO=b.TAGNO from catalog a,(select mid=dbo.f_getmergid(id)+'%',TAGNO from catalog where TAGNO<>'') b where a.TAGNO='' and dbo.f_getmergid(id) like b.mid
update a set a.tagno=b.tagno from catalog a inner join catalog b on a.parent_id=b.id
update catalog set tagno=b.tagno from catalog,catalog b where b.id=catalog.parent_id
一个循环即可。 create table #catalog(id int,parent_id int,TAGNO varchar(20)) insert #catalog values(100, 1 ,'100-01-001') insert #catalog values(101 , 1 , '100-01-002') insert #catalog values(500 , 100 ,null) insert #catalog values(501 ,100 ,null) insert #catalog values(502 ,100,null) insert #catalog values(503 , 100,null) insert #catalog values(800 , 101 ,null) insert #catalog values(801 , 101,null) insert #catalog values(802 , 101,null) --开始while exists(select 1 from #catalog where parent_id in (select id from #catalog where tagno is not null) and tagno is null) update #catalog set tagno=(select tem.tagno from #catalog tem where tem.id=#catalog.parent_id and tem.tagno is not null) where parent_id in (select id from #catalog where tagno is not null) and tagno is null--结束go drop table #catalog
update catalog a
set a.tagno=b.tagno from catalog b where b.id=a.parent_id
update catalog
set tagno=b.tagno from catalog b where b.id=catalog.parent_id
列前缀 'catalog' 与查询中所用的表名或别名不匹配。
set a.tagno=b.tango
from catalog a,catalog b
where a.parent_id=b.id
set tagno=b.tagno
from catalog,catalog b where b.id=catalog.parent_id
set a.tagno=b.tagno
from catalog a,catalog b
where a.parent_id=b.id
update catalog set tagno=(select tagno from catalog b where b.id=catalog.parent_id) where parent_id in (select parent_id from catalog)
create function f_getmergid(@id int)
returns varchar(8000)
as
begin
declare @re varchar(8000),@pid int --为了数字排序正常,需要统一编码宽度
declare @idlen int,@idheader varchar(20)
select @idlen=max(len(id))
,@idheader=space(@idlen)
from catalog --得到编码累计
set @re=right(@idheader+cast(@id as varchar),@idlen)
select @pid=parent_id from catalog where id=@id
while @@rowcount>0
select @re=right(@idheader+cast(@pid as varchar),@idlen)+','+@re
,@pid=parent_id from catalog where id=@pid
return(@re)
end
go--调用这个函数进行更新处理update catalog set TAGNO=b.TAGNO
from catalog a,(select mid=dbo.f_getmergid(id)+'%',TAGNO from catalog where TAGNO<>'') b
where a.TAGNO='' and dbo.f_getmergid(id) like b.mid
http://expert.csdn.net/Expert/topic/2285/2285830.xml?temp=1.794696E-04
set a.tagno=b.tagno
from catalog a inner join catalog b on a.parent_id=b.id
set tagno=b.tagno
from catalog,catalog b where b.id=catalog.parent_id
create table #catalog(id int,parent_id int,TAGNO varchar(20))
insert #catalog values(100, 1 ,'100-01-001')
insert #catalog values(101 , 1 , '100-01-002')
insert #catalog values(500 , 100 ,null)
insert #catalog values(501 ,100 ,null)
insert #catalog values(502 ,100,null)
insert #catalog values(503 , 100,null)
insert #catalog values(800 , 101 ,null)
insert #catalog values(801 , 101,null)
insert #catalog values(802 , 101,null)
--开始while exists(select 1 from #catalog where parent_id in (select id from #catalog where tagno is not null) and tagno is null)
update #catalog set tagno=(select tem.tagno from #catalog tem where tem.id=#catalog.parent_id and tem.tagno is not null) where parent_id in (select id from #catalog where tagno is not null) and tagno is null--结束go
drop table #catalog