使用加入更新目标列更新一次

Question

我需要在nvarchar（max）列中替换多个子字符串。子字符串和相应替换值的列表在单独的表中。当我使用JOIN运行UPDATE语句时，如果我在目标表中每行要替换一个以上的子字符串，则替换仅发生一次。

下面是说明问题的示例代码，请注意，对于id=1和rev=1，我在目标表中有两个值可以替换：

CREATE TABLE dbo.replacements
  (
     id          INT NOT NULL,
     rev         INT NOT NULL,
     target      NVARCHAR(50) NOT NULL,
     replacement NVARCHAR(50) NOT NULL,
  )
go
INSERT INTO dbo.replacements VALUES (1, 1, 'abc', '123');
INSERT INTO dbo.replacements VALUES (1, 1, 'xyz', '789');
INSERT INTO dbo.replacements VALUES (2, 1, 'jkf', '321');
go
CREATE TABLE dbo.destination
  (
     id          INT NOT NULL,
     rev         INT NOT NULL,
     description NVARCHAR(max) NOT NULL
  )
go
INSERT INTO dbo.destination VALUES (1, 1, 'These two strings abc and xyz are to be replaced');
INSERT INTO dbo.destination VALUES (2, 1, 'This text jkf is to be replaced');
go
SELECT *
FROM   dbo.replacements m
       INNER JOIN dbo.destination d ON m.id = d.id AND m.rev = d.rev
UPDATE d
SET    d.description = Replace(d.description, m.target, m.replacement)
FROM   dbo.destination d 
       INNER JOIN dbo.replacements m ON m.id = d.id AND m.rev = d.rev
SELECT *
FROM   dbo.replacements m
       INNER JOIN dbo.destination d ON m.id = d.id AND m.rev = d.rev 

预计第1行的值'These two strings 123 and 789 are to be replaced'，但替换的实际结果是：

id          rev         description
----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           1           These two strings 123 and xyz are to be replaced
1           1           These two strings 123 and xyz are to be replaced
2           1           This text 321 is to be replaced

任何建议都非常感谢。

编辑：

同一行可能有2个以上的替换。

Answer 1

我在优化和清洁方面没有花太多时间在这方面，但我会嵌套替换，并添加另一个加入：

CREATE TABLE #replacements
  (
     id          INT NOT NULL,
     rev         INT NOT NULL,
     target      NVARCHAR(50) NOT NULL,
     replacement NVARCHAR(50) NOT NULL,
  )
go
INSERT INTO #replacements VALUES (1, 1, 'abc', '123');
INSERT INTO #replacements VALUES (1, 1, 'xyz', '789');
INSERT INTO #replacements VALUES (2, 1, 'jkf', '321');
go
CREATE TABLE #destination
  (
     id          INT NOT NULL,
     rev         INT NOT NULL,
     description NVARCHAR(max) NOT NULL
  )
go
INSERT INTO #destination VALUES (1, 1, 'These two strings abc and xyz are to be replaced');
INSERT INTO #destination VALUES (2, 1, 'This text jkf is to be replaced');
go
SELECT *
FROM   #replacements m
       INNER JOIN #destination d ON m.id = d.id AND m.rev = d.rev
UPDATE d
SET    d.description = Replace(REPLACE(d.description, m.target, m.replacement), m2.target, m2.replacement)
FROM   #replacements m
       INNER JOIN #destination d ON m.id = d.id AND m.rev = d.rev
       INNER JOIN #replacements m2 ON m2.id = d.id AND m2.id = d.rev
WHERE m.target <> m2.target
SELECT *
FROM   #replacements m
       INNER JOIN #destination d ON m.id = d.id AND m.rev = d.rev