MySQL で複数の行の複数の列を異なる値で更新する

Mehvish Ashiq 2024年2月16日

MySQL MySQL Column

この記事では、CASE ステートメント、IF() 関数、INSERT ... ON DUPLICATE KEY UPDATE 句、UPDATE と JOIN() 関数を使用して複数の列を更新する方法を学びます。 MySQL で異なる値を持つ複数の行。

MySQL で複数のレコード (行) の複数の列を異なる値で更新する

場合によっては、データベース内の複数の行の複数の列を異なる値で更新する必要があります。テーブルにいくつかのレコードがある場合は、複数の UPDATE ステートメントを使用しても問題ありません。

テーブルに何百万もの行があるとします。テーブルを更新する方法のいくつかを以下に示します。

CASE ステートメントを使用します。
IF() 関数を使用します。
INSERT ... ON DUPLICATE KEY UPDATE を使用します。
JOIN() で UPDATE を使用します。

上記のアプローチを学習するには、ID が主キーである属性 (列) として ID、JavaScore、および PythonScore を持つ students という名前のテーブルを作成します。このチュートリアルに従って、以下のクエリを使用してテーブルを作成および設定できます。

コード例:

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

出力：

ID	Javaスコア	PythonScore
1	70	65
2	75	80
3	81	89
4	50	70

students テーブルを作成してデータを入力したら、前述の方法を使用できます。

`CASE` ステートメントを使用する

コード例:

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

SELECT ステートメントを使用して、更新された結果を取得します。

SELECT * FROM students;

出力：

ID	Javaスコア	PythonScore
1	75	70
2	80	85
3	86	94
4	55	75

すべての条件を通過し、最初の条件が満たされたときに項目 (値) を出力する CASE ステートメントを使用して、複数の行の複数の列を異なる値で更新します (if-then-else ステートメントのように)。条件が TRUE になると読み取りを停止し、対応する結果を返します。

TRUE 条件がないと仮定すると、ELSE 部分が実行されます。 ELSE セクションがない場合は、NULL を返します。

すべてのレコードで一定に保ちたいDATETIMEタイプの別のフィールドがある場合、クエリは次のようになります。

コード例:

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

`IF()` 関数を使用する

コード例:

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

SELECT コマンドを実行して、students テーブルの新しい値を取得します。

SELECT * FROM students;

出力：

ID	Javaスコア	PythonScore
1	76	71
2	81	86
3	87	95
4	56	76

条件が満たされた場合に特定の値を返す IF() 関数を使用します。それ以外の場合は、指定された別の値を返します。構文は IF(条件, TrueValue, FalseValue) です。

ID=1 条件が満たされている場合、なぜ別の IF() に行くのかという疑問があるかもしれません。次のようにネストされた IF() 関数を使用して、複数の IFs を作成します。

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

もっと分かりやすくしましょう。次のスニペットでは、複数の IF があり、条件が満たされているかどうかは問題ではありません。

すべての IF 条件がチェックされ、それに応じて値が設定されます。最後の IF には ELSE 部分があり、4 番目の IF 条件が FALSE の場合にのみ実行されます。

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

ネストされた IF() 関数を使用する理由は、複数の行を異なる値で更新するためです。

複数の行の複数の列を更新する必要がある場合は、ネストされた IF() 関数よりも理解しやすく管理しやすいため、CASE ステートメントを使用することをお勧めします。

`INSERT ... ON DUPLICATE KEY UPDATE` を使用

コード例:

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

出力：

ID	Javaスコア	PythonScore
1	77	72
2	82	87
3	88	96
4	57	77

この例は INSERT ... ON DUPLICATE KEY UPDATE を示しています。通常、PRIMARY KEY または UNIQUE インデックスで重複が発生する可能性のある特定のテーブルに INSERT すると、エラーが発生します。

ただし、ON DUPLICATE KEY UPDATE を指定すると、MySQL は既存のレコードを最新の値で更新します。 PRIMARY KEY に重複が見つかった場合、その特定の列の値は現在の値に設定されます。

このチュートリアルを書いているとき、VALUES() 関数は機能していますが、VALUES() 関数は非推奨であり、将来のリリースで削除されるという警告が表示されます。さらに支援が必要な場合は、MySQL Documentation を検討してください。

`UPDATE` を `JOIN()` とともに使用する

コード例:

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;

このソリューションは、セーフモード が無効になっている場合にのみ機能します。 Edit->Preference->SQL Editor に移動し、Safe Mode オプションのチェックを外すことで、MySQL Workbench で無効にすることができます。

次に、MySQL サーバーを再起動し、上記のクエリを実行して、SELECT * FROM students; を使用します。コマンドを実行して、次の結果を取得します。

出力：

ID	Javaスコア	PythonScore
1	78	73
2	83	88
3	89	97
4	58	78

SELECT と UNION ALL を使用して、JOIN() 内のデータを収集します。それが完了したら、JOIN() を使用してすべてのデータを結合し、ID 属性のすべての条件を満たす条件で JavaScore と PythonScore を設定します。

著者： Mehvish Ashiq

Mehvish Ashiq is a former Java Programmer and a Data Science enthusiast who leverages her expertise to help others to learn and grow by creating interesting, useful, and reader-friendly content in Computer Programming, Data Science, and Technology.

LinkedIn GitHub Facebook

MySQL で複数のレコード (行) の複数の列を異なる値で更新する

CASE ステートメントを使用する

IF() 関数を使用する

INSERT ... ON DUPLICATE KEY UPDATE を使用

UPDATE を JOIN() とともに使用する

関連記事 - MySQL Column

`CASE` ステートメントを使用する

`IF()` 関数を使用する

`INSERT ... ON DUPLICATE KEY UPDATE` を使用

`UPDATE` を `JOIN()` とともに使用する