Actualice varias columnas en varias filas con diferentes valores en MySQL

En este artículo, aprenderemos el uso de la instrucción CASE, la función IF(), la cláusula INSERT ... ON DUPLICATE KEY UPDATE y la función UPDATE con JOIN() para actualizar varias columnas. en varias filas con diferentes valores en MySQL.

Actualice varias columnas en varios registros (filas) con diferentes valores en MySQL

A veces, necesitamos actualizar varias columnas en varias filas con diferentes valores en la base de datos. Está bien usar múltiples declaraciones UPDATE si tenemos algunos registros en la tabla.

Supongamos que hay millones de filas en la tabla. Algunas de las formas de actualizar la tabla se enumeran a continuación.

1. Utilice la sentencia CASE.
2. Utilice la función SI().
3. Utilice INSERTAR... AL ACTUALIZAR CLAVE DUPLICADA.
4. Utilice ACTUALIZAR con JOIN().

Para aprender los enfoques mencionados anteriormente, cree una tabla llamada “estudiantes” que tenga ID, JavaScore y PythonScore como atributos (columnas) donde ID es una clave principal. Puede seguir este tutorial utilizando las consultas a continuación para crear y completar la tabla.

Código de ejemplo:

# create a table
CREATE TABLE students(
  ID INT NOT NULL,
  JavaScore INT NOT NULL,
  PythonScore INT NOT NULL,
  PRIMARY KEY (ID));

# insert data
INSERT INTO students (ID, JavaScore, PythonScore)
VALUES
(1, 70, 65),
(2, 75, 80),
(3, 81, 89),
(4, 50, 70);

# display table data
SELECT * FROM students;

Producción:

Una vez que se crea y completa la tabla de estudiantes, podemos usar los enfoques mencionados.

Utilice la declaración CASE

Código de ejemplo:

UPDATE students
    SET JavaScore = (case
                    when ID = 1 then 75
                    when ID = 2 then 80
                    when ID = 3 then 86
                    when ID = 4 then 55
                    end),
        PythonScore = (case
                    when ID = 1 then 70
                    when ID = 2 then 85
                    when ID = 3 then 94
                    when ID = 4 then 75
                    end)
    WHERE ID in (1,2,3,4);

Utilice la instrucción SELECT para obtener los resultados actualizados.

SELECT * FROM students;

Producción:

Actualizamos varias columnas en varias filas con diferentes valores usando la declaración CASE que pasa por todas las condiciones y genera un elemento (valor) cuando se cumple la primera condición (como la declaración if-then-else). Deja de leer una vez que la condición es VERDADERA y devuelve el resultado correspondiente.

Supongamos que no hay condiciones VERDADERAS, entonces se ejecuta la parte SINO. En ausencia de la sección ELSE, devuelve NULL.

Si hay otro campo del tipo DATETIME que queremos mantener constante para todos los registros, la consulta sería la siguiente.

Código de ejemplo:

UPDATE students
    SET JavaScore = (case
                    when ID = 1 then 75
                    when ID = 2 then 80
                    when ID = 3 then 86
                    when ID = 4 then 55
                    end),
        PythonScore = (case
                    when ID = 1 then 70
                    when ID = 2 then 85
                    when ID = 3 then 94
                    when ID = 4 then 75
                    end),
        DATEANDTIME = NOW()

    WHERE ID in (1,2,3,4);

Usa la función SI()

Código de ejemplo:

UPDATE students SET
    JavaScore = IF(ID=1,76,IF(ID=2,81,IF(ID=3,87,IF(ID=4,56,NULL)))),
    PythonScore = IF(ID=1,71,IF(ID=2,86,IF(ID=3,95,IF(ID=4,76,NULL))))
WHERE ID IN (1,2,3,4);

Ejecute el comando SELECT para obtener los nuevos valores de la tabla students.

SELECT * FROM students;

Producción:

Usamos la función IF() que devuelve un valor particular si se cumple la condición. De lo contrario, devuelve otro valor especificado. Su sintaxis es IF(condición, TrueValue, FalseValue).

Es posible que tenga una pregunta si se cumple la condición ID=1, entonces, ¿por qué va a otro IF()? Usamos funciones IF() anidadas de la siguiente manera para crear múltiples IF().

IF(condition, TrueValue,
  IF(condition, TrueValue,
    IF(condition, TrueValue,
      IF(condition, TrueValue, FalseValue)
      )
    )
  )

Hagámoslo más fácil de entender. En el siguiente fragmento, tenemos múltiples if, y no importa si la condición se cumple o no.

Cada condición if se verificará y establecerá el valor en consecuencia. El último IF tiene la parte ELSE, que solo se ejecutará si la cuarta condición IF es FALSE.

IF Condition
    TrueValue
IF Condition
    TrueValue
IF Condition
    TrueValue
IF Condition
    TrueValue
ELSE
    FalseValue

La razón para usar las funciones IF() anidadas es actualizar varias filas con diferentes valores.

Cuando es necesario actualizar varias columnas en varias filas, preferimos usar la declaración CASE porque es más fácil de entender y administrar que las funciones IF() anidadas.

Utilice INSERTAR... EN ACTUALIZACIÓN DE CLAVE DUPLICADA

Código de ejemplo:

INSERT INTO students (ID, JavaScore, PythonScore)
VALUES
(1, 77, 72),(2, 82, 87),(3, 88, 96),(4, 57, 77)
ON DUPLICATE KEY UPDATE
JavaScore = VALUES(JavaScore),
PythonScore = VALUES(PythonScore);

Producción:

Este ejemplo muestra INSERTAR... AL ACTUALIZAR CLAVE DUPLICADA. Normalmente, cuando INSERTAMOS en una tabla en particular donde puede causar un duplicado en el índice PRIMARY KEY o UNIQUE, genera un error.

Sin embargo, MySQL actualiza los registros existentes con los últimos valores si especificamos ON DUPLICATE KEY UPDATE. Si se encuentra un duplicado en CLAVE PRINCIPAL, el valor de esa columna en particular se establecerá en su valor actual.

Aunque la función VALUES() está funcionando al escribir este tutorial, muestra una advertencia de que la función VALUES() está obsoleta y se eliminará en una versión futura. Puede considerar Documentación de MySQL para obtener más ayuda.

Use ACTUALIZAR con JOIN()

Código de ejemplo:

UPDATE students std
JOIN (
    SELECT 1 AS ID, 78 AS JavaScore, 73 AS PythonScore
    UNION ALL
    SELECT 2 AS ID, 83 AS JavaScore, 88 AS PythonScore
    UNION ALL
    SELECT 3 AS ID, 89 AS JavaScore, 97 AS PythonScore
    UNION ALL
    SELECT 4 AS ID, 58 AS JavaScore, 78 AS PythonScore
) temp
ON std.ID = temp.ID
SET std.JavaScore = temp.JavaScore, std.PythonScore = temp.PythonScore;

Esta solución solo funcionará si el modo seguro está deshabilitado. Podemos desactivarlo en MySQL Workbench yendo a Editar->Preferencias->Editor SQL y desmarcando la opción Modo seguro.

Luego, reinicie el servidor MySQL, ejecute la consulta dada anteriormente y use SELECCIONAR * DE estudiantes; comando para obtener los siguientes resultados.

Producción:

Recopilamos los datos dentro de JOIN() usando SELECT y UNION ALL. Una vez hecho esto, unimos todos los datos usando JOIN() y configuramos JavaScore y PythonScore en cada condición satisfactoria para el atributo ID.