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

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

从'a-1'到'a1',

从'a.1'到'a1',

从'b-1'到'b1',

从'b.1'到'b1'.

现在,我正在执行两个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')

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

你可能已经猜到了 - 我有一个巨大的表来处理(一个语句在大约90秒内完成),我有大量的更新要执行.

那么,是否可以合并更新,以便只扫描一次表？或许,在这样的情况下,有更好的方法来处理.

编辑:注意,我正在使用的真实数据和我必须执行的数据的更改并不是那么简单 - 字符串更长并且它们不遵循任何模式(它是用户数据,所以没有假设可以制作 - 它可以是任何东西).

您可以使用一个语句和一些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

当然,这会导致对每条记录进行写入,而对于索引,这可能是一个问题,因此您只能过滤掉要更改的行:

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')

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

在更一般的情况下,每个新值可能有数百个映射,您将创建一个单独的旧值和新值表,然后在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);

两个选择陈述都至关重要.第一个是相关的子查询(不一定快,但如果映射器表有数千行,则比大多数备选方案更快)将新值拉出对应于旧值的映射表.第二个确保只修改映射表中具有值的那些行; 这是至关重要的,否则,对于那些没有映射条目的行,标题将被设置为null(那些是你开始之前没有的记录).

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

解决乔纳森的回答.

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

他的初始版本需要对mapper表进行大量读取.