¿Es posible realizar varias actualizaciones con una sola instrucción SQL UPDATE?

Question

¿Es posible realizar varias actualizaciones con una sola instrucción SQL UPDATE?

Digamos que tengo una tabla tbl con columnas id y título. Necesito cambiar todos los valores de la columna título:

de "a-1" a "a1",
de "a. 1" a "a1",
de "b-1" a "b1",
de "b.1" a "b1".

Ahora mismo, estoy realizando dos declaraciones de ACTUALIZACIÓN:

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

Esto no es en absoluto un problema, si la tabla es pequeña, y la instrucción individual se completa en menos de un segundo y solo necesita unos pocos sentencias a ejecutar.

Probablemente lo hayas adivinado - Tengo una gran tabla con la que lidiar (una declaración se completa en unos 90 segundos), y tengo un gran número de actualizaciones que realizar.

Entonces, ¿es posible fusionar las actualizaciones para que solo escanee la tabla una vez? O tal vez, hay una mejor manera de lidiar con una situación como esta.

EDITAR: Tenga en cuenta que los datos reales con los que estoy trabajando y los cambios a los datos que tengo que realizar no son realmente tan simples: las cadenas son más tiempo y no siguen ningún patrón (son datos del usuario, por lo que no se pueden hacer suposiciones, puede ser cualquier cosa).

27

sql sql-server tsql optimization sql-server-2005

Author: Paulius, 2009-01-05

Source

5 answers

Puede usar una sentencia y varias sentencias 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

Por supuesto, esto causará una escritura en cada registro, y con los índices, puede ser un problema, por lo que puede filtrar solo las filas que desea cambiar:

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

Eso reducirá el número de escrituras en la tabla.

23

Author: casperOne,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-01-05 02:06:06

Trabajando con la respuesta de Jonathan.

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

Su versión inicial requeriría un gran número de lecturas en la tabla mapper.

9

Author: mrdenny,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-01-05 08:13:49

Si las transformaciones son tan simples como sus ejemplos, podría hacer la actualización con un poco de manipulación de cadenas:

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

¿Funcionaría algo así para ti?

3

Author: Matt Hamilton,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-01-05 02:05:25

O

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

0

Author: Charles Bretana,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-01-05 02:15:25

score 22 · Accepted Answer

En un caso más general, donde podría haber muchos cientos de asignaciones a cada uno de los nuevos valores, crearía una tabla separada de los valores antiguos y nuevos, y luego la usaría en la instrucción UPDATE. En un dialecto de 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);

Ambas sentencias select son cruciales. La primera es una sub-consulta correlacionada (no necesariamente rápida, pero más rápida que la mayoría de las alternativas si la tabla mapper tiene miles de filas) que extrae el nuevo valor de la tabla de asignación que corresponde al viejo valor. El segundo garantiza que solo se modifiquen las filas que tienen un valor en la tabla de asignación; esto es crucial, ya que de lo contrario, el título se establecerá en null para aquellas filas sin una entrada de asignación (y esos eran los registros que estaban bien antes de comenzar).

Para algunas alternativas, las operaciones de CASO están bien. Pero si tiene cientos, miles o millones de asignaciones que realizar, es probable que exceda los límites de la longitud de la instrucción SQL en su DBMS.