一尘不染

执行联接时多次更新一行

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


阅读 94

收藏
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