Возможно ли выполнить несколько обновлений с помощью одного оператора UPDATE SQL?

Скажем, у меня есть таблица tbl с идентификаторами столбцов и заголовком. Мне нужно изменить все значения столбца заголовка:

от '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 секунд), и у меня есть огромное количество обновлений для выполнения.

Итак, возможно ли объединить обновления, чтобы он только сканировал таблицу один раз? Или, возможно, есть лучший способ справиться в такой ситуации.

EDIT: обратите внимание, что реальные данные, с которыми я работаю, и изменения данных, которые я должен выполнить, на самом деле не так просты - строки длиннее и они не следуют ни одному шаблону (это данные пользователя, поэтому никаких предположений не может быть сделано - это может быть что угодно).

Ответ 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 в порядке. Но если у вас есть сотни или тысячи или миллионы отображений, то вы, вероятно, превысите пределы длины инструкции SQL в вашей СУБД.

Ответ 2

Вы можете использовать один оператор и ряд операторов 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')

Это сократит количество записей в таблице.

Ответ 3

Отработка ответа Джонатана.

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

Его первоначальная версия потребует большого количества чтений в таблице сопоставления.

Ответ 4

Если преобразования такие же простые, как ваши примеры, вы можете сделать обновление с небольшим количеством манипуляций с строкой:

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

Будет ли что-то подобное работать для вас?

Ответ 5

Или

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