SELECT A.CONV_LINE_OID, REPLACE(REPLACE(A.ERROR_MSG, '%s1', B.CLIENT), '%s2', B.fACILITY) FROM CONV_LINE A, FACILITY_GROUP B WHERE A.CONV_LINE_OID = B.CONV_LINE_OID;
go create table conv_line( conv_line_oid varchar(4), error_msg varchar(50) ) go insert conv_line select '1001','client %s1 not facility %s2' union all select '1002','client %s1 not facility %s2'go create table facility_group( facility_group_oid varchar(4), client varchar(10), facility varchar(10), conv_line_oid varchar(4) ) go insert facility_group select '2001','clientA','facilityA','1001' union all select '2002','clientB','facilityB','1002' update conv_line set error_msg=replace(REPLACE(error_msg,'%s1',client),'%s2',facility) from facility_group a where a.conv_line_oid=conv_line.conv_line_oidselect * from conv_line/* conv_line_oid error_msg 1001 client clientA not facility facilityA 1002 client clientB not facility facilityB */MSSQL语法,楼主修改数据类型即可
REPLACE(REPLACE(A.ERROR_MSG, '%s1', B.CLIENT), '%s2', B.fACILITY)
FROM CONV_LINE A, FACILITY_GROUP B
WHERE A.CONV_LINE_OID = B.CONV_LINE_OID;
go
create table conv_line(
conv_line_oid varchar(4),
error_msg varchar(50)
)
go
insert conv_line
select '1001','client %s1 not facility %s2' union all
select '1002','client %s1 not facility %s2'go
create table facility_group(
facility_group_oid varchar(4),
client varchar(10),
facility varchar(10),
conv_line_oid varchar(4)
)
go
insert facility_group
select '2001','clientA','facilityA','1001' union all
select '2002','clientB','facilityB','1002'
update conv_line
set error_msg=replace(REPLACE(error_msg,'%s1',client),'%s2',facility)
from facility_group a where a.conv_line_oid=conv_line.conv_line_oidselect * from conv_line/*
conv_line_oid error_msg
1001 client clientA not facility facilityA
1002 client clientB not facility facilityB
*/MSSQL语法,楼主修改数据类型即可
在oracle中没有update from,这个语法是sql server的,
虽然可以通过子查询的方式来实现update from的功能:
但是如果把replace也加进去,好像就有问题了,也就是说replace中不能写select语句吧
建议再看一下这两张表,是否存在 一对多的关系,否则,需要微调一下SQL。