USE tempdb
GO
IF OBJECT_ID('dbo.[user_data]') IS NOT NULL
DROP TABLE dbo.user_data
GO
CREATE TABLE dbo.user_data(
[user_ID] INT
,[usr_Account] VARCHAR(20)
,[usr_Name] NVARCHAR(20)
,[usr_Password] VARCHAR(20)
,[usr_Super] BIT
)
GO
IF OBJECT_ID('dbo.[custom_data]') IS NOT NULL
DROP TABLE dbo.custom_data
GO
CREATE TABLE dbo.custom_data(
[cust_ID] INT
,[cust_No] VARCHAR(10)
,[cust_Name] NVARCHAR(10)
,[cust_Admin] VARCHAR(30)
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[user_data] VALUES(1,'aabbcc','陈s','abc123',1);
INSERT INTO dbo.[user_data] VALUES(4,'qqaa1','一棵小草','qq112233',0);
INSERT INTO dbo.[user_data] VALUES(5,'qqaaz','小傻子','wweedsw12',0);
INSERT INTO dbo.[user_data] VALUES(2,'test','测试账号','qweasd1',0);
INSERT INTO dbo.[user_data] VALUES(3,'testacc','花前月下','qqww21',0);
--
INSERT INTO dbo.custom_data VALUES(1,'C001','A公司','<aabbcc><qqaaz>');
INSERT INTO dbo.custom_data VALUES(2,'C002','B公司','<test><qqaaz>');
INSERT INTO dbo.custom_data VALUES(3,'C003','C公司','<testacc>');
------ 以上为测试表及测试数据 ------
--更新
;WITH cte AS (
SELECT *
,REPLACE(REPLACE((SELECT '【'+B.usr_Name+'】' FROM dbo.user_data AS B WHERE A.cust_Admin LIKE '%<'+B.usr_Account+'>%' FOR XML PATH('')),'【','<'),'】','>') AS cust_Admin_replace
FROM custom_data AS A
)
/*
select * from cte
*/
UPDATE cte
SET cust_Admin = cust_Admin_replace
--查询更新结果
SELECT * FROM dbo.custom_data AS cd
/*
cust_ID cust_No cust_Name cust_Admin
----------- ---------- ---------- ------------------------------
1 C001 A公司 <陈s><小傻子>
2 C002 B公司 <小傻子><测试账号>
3 C003 C公司 <花前月下>
*/
FOR XML PATH('')
-- 借2楼版主数据
alter function fn(@str varchar(200))
returns varchar(200)
as
begin
declare @result varchar(200) = @str
declare @account varchar(30), @name varchar(30)
declare sss cursor for select usr_account, usr_name from user_data
open sss
fetch sss into @account, @name
while @@FETCH_STATUS >= 0
begin
set @result = replace(@result, '<' + @account + '>', '<' + @name + '>')
fetch sss into @account, @name
end
close sss
deallocate sss
return @result
end
go
select *, dbo.fn([cust_Admin]) from custom_data
CREATE TABLE 用户表(
[user_ID] INT
,[usr_Account] VARCHAR(20)
,[usr_Name] NVARCHAR(20)
,[usr_Password] VARCHAR(20)
,[usr_Super] BIT)
CREATE TABLE 客户表(
[cust_ID] INT
,[cust_No] VARCHAR(10)
,[cust_Name] NVARCHAR(10)
,[cust_Admin] VARCHAR(30))
INSERT INTO 用户表 VALUES(1,'aabbcc','陈s','abc123',1);
INSERT INTO 用户表 VALUES(4,'qqaa1','一棵小草','qq112233',0);
INSERT INTO 用户表 VALUES(5,'qqaaz','小傻子','wweedsw12',0);
INSERT INTO 用户表 VALUES(2,'test','测试账号','qweasd1',0);
INSERT INTO 用户表 VALUES(3,'testacc','花前月下','qqww21',0);
INSERT INTO 客户表 VALUES(1,'C001','A公司','<aabbcc><qqaaz>');
INSERT INTO 客户表 VALUES(2,'C002','B公司','<test><qqaaz>');
INSERT INTO 客户表 VALUES(3,'C003','C公司','<testacc>');
-- 更新客户表cust_Admin字段
declare @tsql nvarchar(max),@Account nvarchar(20),@Name nvarchar(20)
declare ap scroll cursor for select usr_Account,usr_Name from 用户表
open ap
fetch first from ap into @Account,@Name
while(@@fetch_status<>-1)
begin
select @tsql=N'update 客户表 set cust_Admin=replace(cust_Admin,''<'+@Account+N'>'',''<'+@Name+N'>'') '
--print @tsql
exec(@tsql)
fetch next from ap into @Account,@Name
end
close ap
deallocate ap
-- 结果
select * from 客户表
/*
cust_ID cust_No cust_Name cust_Admin
----------- ---------- ---------- ------------------------------
1 C001 A公司 <陈s><小傻子>
2 C002 B公司 <测试账号><小傻子>
3 C003 C公司 <花前月下>
(3 row(s) affected)
*/
