Aktualisieren Sie mehrere Spalten in mehreren Zeilen mit unterschiedlichen Werten in MySQL

In diesem Artikel lernen wir die Verwendung der CASE-Anweisung, der IF()-Funktion, der INSERT ... ON DUPLICATE KEY UPDATE-Klausel und UPDATE mit der JOIN()-Funktion kennen, um mehrere Spalten zu aktualisieren in mehreren Zeilen mit unterschiedlichen Werten in MySQL.

Aktualisieren Sie mehrere Spalten in mehreren Datensätzen (Zeilen) mit unterschiedlichen Werten in MySQL

Manchmal müssen wir mehrere Spalten in mehreren Zeilen mit unterschiedlichen Werten in der Datenbank aktualisieren. Es ist in Ordnung, mehrere UPDATE-Anweisungen zu verwenden, wenn wir einige Datensätze in der Tabelle haben.

Angenommen, die Tabelle enthält Millionen von Zeilen. Einige Möglichkeiten zum Aktualisieren der Tabelle sind unten aufgeführt.

1. Verwenden Sie die CASE-Anweisung.
2. Verwenden Sie die Funktion IF().
3. Verwenden Sie INSERT ... ON DUPLICATE KEY UPDATE.
4. Verwenden Sie UPDATE mit JOIN().

Um die oben genannten Ansätze zu lernen, erstellen Sie eine Tabelle namens students mit ID, JavaScore und PythonScore als Attribute (Spalten), wobei ID ein Primärschlüssel ist. Sie können diesem Lernprogramm folgen, indem Sie die folgenden Abfragen verwenden, um die Tabelle zu erstellen und zu füllen.

Beispielcode:

# 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;

Ausgang:

Sobald die Tabelle Studenten erstellt und ausgefüllt ist, können wir die erwähnten Ansätze verwenden.

Verwenden Sie die CASE-Anweisung

Beispielcode:

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

Verwenden Sie die SELECT-Anweisung, um die aktualisierten Ergebnisse zu erhalten.

SELECT * FROM students;

Ausgang:

Wir aktualisieren mehrere Spalten in mehreren Zeilen mit unterschiedlichen Werten mit der CASE-Anweisung, die alle Bedingungen durchläuft und ein Element (Wert) ausgibt, wenn die erste Bedingung erfüllt ist (wie die if-then-else-Anweisung). Es hört auf zu lesen, sobald die Bedingung TRUE ist und liefert das entsprechende Ergebnis zurück.

Angenommen, es gibt keine TRUE-Bedingungen, dann wird der ELSE-Teil ausgeführt. In Abwesenheit des Abschnitts ELSE wird NULL zurückgegeben.

Wenn es ein weiteres Feld vom Typ DATETIME gibt, das wir für alle Datensätze konstant halten möchten, würde die Abfrage wie folgt aussehen.

Beispielcode:

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

Verwenden Sie die IF()-Funktion

Beispielcode:

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

Führen Sie den Befehl SELECT aus, um die neuen Werte der Tabelle students zu erhalten.

SELECT * FROM students;

Ausgang:

Wir verwenden die Funktion IF(), die einen bestimmten Wert zurückgibt, wenn die Bedingung erfüllt ist. Andernfalls wird ein anderer angegebener Wert zurückgegeben. Seine Syntax ist IF(condition, TrueValue, FalseValue).

Sie haben vielleicht eine Frage, wenn die Bedingung ID=1 erfüllt ist, warum geht es dann zu einem anderen IF()? Wir verwenden verschachtelte IF()-Funktionen wie folgt, um mehrere IFs zu erstellen.

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

Machen wir es einfacher zu verstehen. Im folgenden Snippet haben wir mehrere IFs, und es spielt keine Rolle, ob die Bedingung erfüllt ist oder nicht.

Jede IF-Bedingung würde geprüft und der Wert entsprechend gesetzt. Das letzte IF hat den ELSE-Teil, der nur ausgeführt wird, wenn die vierte IF-Bedingung FALSE ist.

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

Der Grund für die Verwendung verschachtelter IF()-Funktionen besteht darin, mehrere Zeilen mit unterschiedlichen Werten zu aktualisieren.

Wenn mehrere Spalten in mehreren Zeilen aktualisiert werden müssen, bevorzugen wir die Verwendung der CASE-Anweisung, da sie einfacher zu verstehen und zu verwalten ist als die verschachtelten IF()-Funktionen.

Verwenden Sie INSERT ... ON DUPLICATE KEY UPDATE

Beispielcode:

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

Ausgang:

Dieses Beispiel zeigt INSERT ... ON DUPLICATE KEY UPDATE. Normalerweise, wenn wir in eine bestimmte Tabelle INSERT, wo es ein Duplikat im PRIMARY KEY oder UNIQUE Index verursachen kann, verursacht es einen Fehler.

MySQL aktualisiert jedoch die vorhandenen Datensätze mit den neuesten Werten, wenn wir ON DUPLICATE KEY UPDATE angeben. Wenn ein Duplikat in PRIMARY KEY gefunden wird, wird der Wert für diese bestimmte Spalte auf den aktuellen Wert gesetzt.

Obwohl die Funktion VALUES() beim Schreiben dieses Tutorials funktioniert, wird eine Warnung angezeigt, dass die Funktion VALUES() veraltet ist und in einer zukünftigen Version entfernt wird. Weitere Unterstützung finden Sie in der MySQL-Dokumentation.

Verwenden Sie UPDATE mit JOIN()

Beispielcode:

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;

Diese Lösung funktioniert nur, wenn der abgesicherte Modus deaktiviert ist. Wir können es in MySQL Workbench deaktivieren, indem wir zu Bearbeiten->Einstellungen->SQL-Editor gehen und die Option Abgesicherter Modus deaktivieren.

Starten Sie dann den MySQL-Server neu, führen Sie die oben angegebene Abfrage aus und verwenden Sie die Schaltfläche SELECT * FROM students; Befehl, um die folgenden Ergebnisse zu erhalten.

Ausgang:

Wir sammeln die Daten innerhalb des JOIN() mit SELECT und UNION ALL. Sobald dies erledigt ist, verbinden wir alle Daten mit JOIN() und setzen den JavaScore und PythonScore auf jede zufriedenstellende Bedingung für das ID-Attribut.