有两张表,
第一张表a里有C1 C2 C3三个字段,里面存的是产品编号,另外一张表b里存了这些编号对应的名称
现在要把表 a里的值全替换成相应的产品名称
表a c1 c2 c3
23 24 26
表b id name
23 sss
24 ddd
26 aaa
结果是 c1 c2 c3
sss ddd aaa
第一张表a里有C1 C2 C3三个字段,里面存的是产品编号,另外一张表b里存了这些编号对应的名称
现在要把表 a里的值全替换成相应的产品名称
表a c1 c2 c3
23 24 26
表b id name
23 sss
24 ddd
26 aaa
结果是 c1 c2 c3
sss ddd aaa
c2=(select top 1 name from b where a.c2=b.id),
c3=(select top 1 name from b where a.c3=b.id)
from a
update 表a set c1=表b.name from 表b inner join #a on #a.c1=表b.id
update 表a set c2=表b.name from 表b inner join #a on #a.c2=表b.id
update 表a set c3=表b.name from 表b inner join #a on #a.c3=表b.id
drop table tab1
go
if object_id('pubs..tab2') is not null
drop table tab2
go
create table tab1
(
c1 varchar(10),
c2 varchar(10),
c3 varchar(10)
)
create table tab2
(
id varchar(10),
name varchar(10)
)
insert into tab1(c1,c2,c3) values('23','24','26')
insert into tab2(id,name) values('23','sss')
insert into tab2(id,name) values('24','ddd')
insert into tab2(id,name) values('26','aaa')select a.c1,b.c2,c.c3 from
(select c1 from tab1 , tab2 where tab1.c1 = tab2.id) as a,
(select c2 from tab1 , tab2 where tab1.c2 = tab2.id) as b,
(select c3 from tab1 , tab2 where tab1.c3 = tab2.id) as cdrop table tab1
drop table tab2
c1 c2 c3
---------- ---------- ----------
23 24 26(所影响的行数为 1 行)
update a set a.c1=b.name from a join b on a.c1=b.id
依此类推。
(c1 int,
c2 int,
c3 int
)create table #b
(c_id int,
c_name varchar(8)
)
insert into #a
select 23,24,26insert into #b
select 23,'sss'
union all
select 24,'ddd'
union all
select 26,'aaa'select (select c_name from #b where c_id=a.c1),
(select c_name from #b where c_id=a.c2),
(select c_name from #b where c_id=a.c3)
from #a adrop table #a
drop table #b
(c1 varchar(8),
c2 varchar(8),
c3 varchar(8)
)create table #b
(c_id int,
c_name varchar(8)
)
insert into #a
select 23,24,26insert into #b
select 23,'sss'
union all
select 24,'ddd'
union all
select 26,'aaa'
update #a set c1=#b.c_name from #b where #a.c1=#b.c_id
update #a set c2=#b.c_name from #b where #a.c2=#b.c_id
update #a set c3=#b.c_name from #b where #a.c3=#b.c_idselect * from #a
drop table #a
drop table #b