执行联接时多次更新一行

小编典典

执行联接时多次更新一行

sql

CREATE TABLE Replacements 
(
    OldVal nvarchar(max),
    NewVal nvarchar(max)
);

CREATE TABLE Foo
(
    Val nvarchar(max)
);

insert into Replacements values ('old1','new1');
insert into Replacements values ('old2','new2');
insert into Replacements values ('old3','new3');
insert into Replacements values ('old4','new4');

insert into Foo values ('old1');
insert into Foo values ('old3');
insert into Foo values ('old2;old4');

我有一些可能用分号分隔的数据。我需要根据查找表加入数据并用新数据替换旧数据。当数据没有分隔时，这很好用，但如果它被分隔，它只执行第一次更新。

update f
set f.val = Replace(f.val, r.OldVal, r.NewVal)
from Foo f 
inner join replacements r on (CHARINDEX(r.OldVal, f.Val) > 0);

select * from Foo;

Val
new1
new3
new2;old4

如何在同一行上执行多个更新？有没有更好的方法来查找/替换分隔字符串中的字符串？兼容性需要回到 SQL Sever 2014。

http://sqlfiddle.com/#!18/c320d13/9

阅读 129

2022-07-21

共1个答案

小编典典

在 SQL Server 中，UPDATE语句只能影响任何单个语句中目标表中的每一行中的 1 行。

这意味着预期的解决方案是多次执行此更新，其中一个技巧是简单地编写固定次数的更新脚本：

update f
set f.val = Replace(f.val,r.OldVal,r.NewVal)
from
 Foo f inner join replacements r 
on 
(CHARINDEX(r.OldVal,f.Val) > 0);

update f
set f.val = Replace(f.val,r.OldVal,r.NewVal)
from
 Foo f inner join replacements r 
on 
(CHARINDEX(r.OldVal,f.Val) > 0);

http://sqlfiddle.com/#!18/c320d13/10

另一种解决方案是递归应用更新：

WHILE EXISTS (SELECT 1
    from Foo f 
    inner join replacements r on (f.Val = r.OldVal or CHARINDEX(r.OldVal,f.Val) > 0))
BEGIN
    update f
    set f.val = Replace(f.val,r.OldVal,r.NewVal)
    from
     Foo f inner join replacements r 
    on 
    (CHARINDEX(r.OldVal,f.Val) > 0);
END

注意：
此答案基于指定分隔符未知的原始帖子。使用已知的分隔符，UPDATE语句需要更具体，但最终需要多次应用更新。

其他可能的解决方案包括使用 aCURSOR或拆分连接字段，替换标记然后将标记重新连接回分隔字符串。

文档对我们为什么需要这样做有点含糊，但是，如果您UPDATE在目标表上有多个匹配项，则只会应用其中1个匹配项，但不能保证是哪一个，它是不确定的：

更新：最佳实践
在指定 FROM 子句以提供更新操作的条件时要小心。如果 UPDATE 语句包含未指定的 FROM 子句，则该语句的结果是未定义的，即，如果 UPDATE 语句不是确定性的，则每个更新的列出现只有一个值可用。例如，在以下脚本的 UPDATE 语句中，Table1 中的两行都满足 UPDATE 语句中 FROM 子句的条件；但未定义 Table1 中的哪一行用于更新 Table2 中的行。

这已在之前讨论过 SO：SQL 多次更新同一行并且在语句中明确禁止MERGE，这将导致此错误：

The MERGE statement attempted to UPDATE or DELETE the same row more than once.
This happens when a target row matches more than one source row. 
A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times.
Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

2022-07-21