27,581
社区成员




UPDATE tb1
SET bzxh=(SELECT ''+ylsh FROM tb2 WHERE spbh=tb1.spbh FOR XML PATH(''))
create table tb1(spbh varchar(10), bzxh varchar(30))
go
insert into tb1(spbh) values('1wp1103'),('5wpv9047'),('2w7011')
go
create table tb2(spbh varchar(10) ,ylsh varchar(1) , ylbl varchar(10))
go
insert into tb2 values
('1wp1103','w','51'),
('1wp1103','p','49'),
('5wpv9047','w','50'),
('5wpv9047','p','40'),
('5wpv9047','v','10'),
('2w7011','w','100')
go
with m as (
select spbh , (select ylsh + ylbl from tb2 where z.spbh = spbh for xml path('')) x
from tb2 z
group by spbh
)
update tb1
set tb1.bzxh = m.x
from tb1 , m
where tb1.spbh = m.spbh
go
select * from tb1
go
drop table tb1 , tb2
go
spbh bzxh
---------- ------------------------------
1wp1103 w51p49
5wpv9047 w50p40v10
2w7011 w100
(3 行受影响)