求一SQL语句，批量更新的有关问题

求一SQL语句，批量更新的问题
有一对照表t1(都唯一)
Col1 col2
A1 A-1
B2 A-2
C2 D-1
另外一表t2
col1 col2
E-2019N A1-平面纹
E-2019N 60036-B2-平面纹
E-2019N B2-AE-80012-平面纹
E-2019N B2-AE-80012-平面纹
E-2019N AE-C2-80012-平面纹
E-2019N AE-80012-C2-平面纹

求一SQL或是过程，一次性把表t2中col2 列中的A1,B2,C2 替换成表t1 中对应的A-1,A-2,D-1

------解决方案--------------------

SQL code

create table t1(Col1 varchar(10), col2 varchar(10))
insert into t1 values('A1','A-1') 
insert into t1 values('B2','A-2') 
insert into t1 values('C2','D-1')
create table t2(Col1 varchar(10), col2 varchar(30))
insert into t2 values('E-2019N','A1-平面纹') 
insert into t2 values('E-2019N','60036-B2-平面纹')
insert into t2 values('E-2019N','B2-AE-80012-平面纹') 
insert into t2 values('E-2019N','B2-AE-80012-平面纹') 
insert into t2 values('E-2019N','AE-C2-80012-平面纹') 
insert into t2 values('E-2019N','AE-80012-C2-平面纹') 
go

--search
select t2.col1,col2 = replace(t2.col2,t1.col1,t1.col2) from t1,t2 where charindex(t1.col1,t2.col2) > 0
/*
col1       col2              
---------- ------------------
E-2019N    A-1-平面纹
E-2019N    60036-A-2-平面纹
E-2019N    A-2-AE-80012-平面纹
E-2019N    A-2-AE-80012-平面纹
E-2019N    AE-D-1-80012-平面纹
E-2019N    AE-80012-D-1-平面纹

（所影响的行数为 6 行）
*/

--update
update t2
set col2 = replace(t2.col2,t1.col1,t1.col2) 
from t1,t2 where charindex(t1.col1,t2.col2) > 0
select * from t2
/*
col1       col2              
---------- ------------------
E-2019N    A-1-平面纹
E-2019N    60036-A-2-平面纹
E-2019N    A-2-AE-80012-平面纹
E-2019N    A-2-AE-80012-平面纹
E-2019N    AE-D-1-80012-平面纹
E-2019N    AE-80012-D-1-平面纹

（所影响的行数为 6 行）
*/

drop table t1,t2

------解决方案--------------------

SQL code

declare @table1 table (col1 varchar(2), col2 varchar(3))

insert into @table1
select 'A1','A-1'
union 
select 'B2','A-2'
union
select 'C2','D-1'
    
declare @table2 table (col1 varchar(7), col2 varchar(50))
insert into @table2
select 'E-2019N', 'A1-pmw' 
union
select 'E-2019N', '60036-B2-pmw'
union
select 'E-2019N', 'B2-AE-80012-pmw'
union
select 'E-2019N', 'B2-AE-80012-pmw'
union
select 'E-2019N', 'AE-C2-80012-pmw'
union
select 'E-2019N', 'AE-80012-C2-pmw'

select * from @table1
select * from @table2

update y
   set y.col2 = replace(y.col2, x.col1, x.col2)
from @table1 x,
     @table2 y
where y.col2 like '%' + x.col1 +  '%'

select * from @table2

求一SQL语句，批量更新的有关问题

相关推荐