求一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--searchselect 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 行）*/--updateupdate t2set col2 = replace(t2.col2,t1.col1,t1.col2) from t1,t2 where charindex(t1.col1,t2.col2) > 0select * 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 @table1select 'A1','A-1'union select 'B2','A-2'unionselect 'C2','D-1'    declare @table2 table (col1 varchar(7), col2 varchar(50))insert into @table2select 'E-2019N', 'A1-pmw' unionselect 'E-2019N', '60036-B2-pmw'unionselect 'E-2019N', 'B2-AE-80012-pmw'unionselect 'E-2019N', 'B2-AE-80012-pmw'unionselect 'E-2019N', 'AE-C2-80012-pmw'unionselect 'E-2019N', 'AE-80012-C2-pmw'select * from @table1select * from @table2update y   set y.col2 = replace(y.col2, x.col1, x.col2)from @table1 x,     @table2 ywhere y.col2 like '%' + x.col1 +  '%'select * from @table2