是否可以使用单个 UPDATE SQL 语句执行多个更新？

Is it possible to perform multiple updates with a single UPDATE SQL statement?

提问by Paulius

Let's say I have a table tblwith columns idand title. I need to change all values of title column:

假设我有一个包含id和title列的表tbl。我需要更改标题列的所有值：

1. from 'a-1' to 'a1',
2. from 'a.1' to 'a1',
3. from 'b-1' to 'b1',
4. from 'b.1' to 'b1'.

1. 从“a-1”到“a1”，
2. 从“a.1”到“a1”，
3. 从“b-1”到“b1”，
4. 从“b.1”到“b1”。

Right now, I'm performing two UPDATE statements:

现在，我正在执行两个 UPDATE 语句：

UPDATE tbl SET title='a1' WHERE title IN ('a-1', 'a.1')
UPDATE tbl SET title='b1' WHERE title IN ('b-1', 'b.1')

This isn't at all a problem, if the table is small, and the single statement completes in less than a second and you only need a few statements to execute.

如果表很小，并且单个语句在不到一秒的时间内完成并且您只需要执行几条语句，那么这根本不是问题。

You probably guested it - I have a huge table to deal with (one statement completes in about 90 seconds), and I have a huge number of updates to perform.

你可能是客套话了——我有一个巨大的表要处理（一个语句在大约 90 秒内完成），我有大量的更新要执行。

So, is it possible to merge the updates so it would only scan the table once? Or perhaps, there's a better way to deal with in a situation like this.

那么，是否可以合并更新以便它只扫描一次表？或者，在这种情况下，有更好的处理方法。

EDIT: Note, that the real data I'm working with and the changes to the data I have to perform are not really that simple - the strings are longer and they don't follow any pattern (it is user data, so no assumptions can be made - it can be anything).

编辑：请注意，我正在使用的真实数据以及我必须执行的数据更改并不是那么简单 - 字符串更长并且它们不遵循任何模式（它是用户数据，因此没有假设可以制作 - 它可以是任何东西）。

采纳答案by Jonathan Leffler

In a more general case, where there could be many hundreds of mappings to each of the new values, you would create a separate table of the old and new values, and then use that in the UPDATE statement. In one dialect of SQL:

在更一般的情况下，每个新值可能有数百个映射，您将创建一个单独的旧值和新值表，然后在 UPDATE 语句中使用它。在 SQL 的一种方言中：

CREATE TEMP TABLE mapper (old_val CHAR(5) NOT NULL, new_val CHAR(5) NOT NULL);
...multiple inserts into mapper...
INSERT INTO mapper(old_val, new_val) VALUES('a.1', 'a1');
INSERT INTO mapper(old_val, new_val) VALUES('a-1', 'a1');
INSERT INTO mapper(old_val, new_val) VALUES('b.1', 'b1');
INSERT INTO mapper(old_val, new_val) VALUES('b-1', 'b1');
...etcetera...

UPDATE tbl
   SET title = (SELECT new_val FROM mapper WHERE old_val = tbl.title)
   WHERE title IN (SELECT old_val FROM mapper);

Both select statements are crucial. The first is a correlated sub-query (not necessarily fast, but faster than most of the alternatives if the mapper table has thousands of rows) that pulls the new value out of the mapping table that corresponds to the old value. The second ensures that only those rows which have a value in the mapping table are modified; this is crucial as otherwise, the title will be set to null for those rows without a mapping entry (and those were the records that were OK before you started out).

两个选择语句都至关重要。第一个是相关子查询（不一定快，但如果映射表有数千行，则比大多数替代查询快），它将新值从对应于旧值的映射表中拉出。第二个确保只有那些在映射表中有值的行被修改；这是至关重要的，否则，对于那些没有映射条目的行，标题将设置为 null（这些记录在您开始之前是正常的）。

For a few alternatives, the CASE operations are OK. But if you have hundreds or thousands or millions of mappings to perform, then you are likely to exceed the limits of the SQL statement length in your DBMS.

对于一些替代方案，CASE 操作是可以的。但是，如果您有数百、数千或数百万个映射要执行，那么您很可能会超出 DBMS 中 SQL 语句长度的限制。

回答by casperOne

You can use one statement and a number of case statements

您可以使用一个语句和多个 case 语句

update tbl
  set title = 
    case
      when title in ('a-1', 'a.1') then 'a1'
      when title in ('b-1', 'b.1') then 'b1'
      else title
    end

Of course, this will cause a write on every record, and with indexes, it can be an issue, so you can filter out only the rows you want to change:

当然，这会导致对每条记录进行写入，并且对于索引，这可能是一个问题，因此您可以仅过滤掉要更改的行：

update tbl
  set title = 
    case
      when title in ('a-1', 'a.1') then 'a1'
      when title in ('b-1', 'b.1') then 'b1'
      else title
    end
where
  title in ('a.1', 'b.1', 'a-1', 'b-1')

That will cut down the number of writes to the table.

这将减少对表的写入次数。

回答by mrdenny

Working off of Jonathan's answer.

解决乔纳森的回答。

UPDATE tbl
   SET title = new_val
FROM mapper
WHERE title IN (SELECT old_val FROM mapper)
     AND mapper.old_val = tbl.title;

His initial version would require a large number of reads to the mapper table.

他的初始版本需要对映射表进行大量读取。

回答by Matt Hamilton

If the transformations are as simple as your examples, you could do the update with a little bit of string manipulation:

如果转换像您的示例一样简单，您可以通过一些字符串操作来进行更新：

UPDATE tbl 
SET title = left(title, 1) + right(title, 1) 
WHERE title IN ('a-1', 'a.1', 'b-1', 'b.1')

Would something like that work for you?

这样的事情对你有用吗？

回答by Charles Bretana

Or

或者

   Update Table set 
     title = Replace(Replace(title, '.', ''), '-', '')
   Where title Like '[ab][.-]1'