SQLiteで既存データを更新する方法

はじめに

データベースにデータが一度入力されると、それがテーブルに存在する間ずっと静的なままであることはほとんどありません。データは、それが表すシステムの変更を反映するように更新され、関連性を保ち、最新の状態を維持します。SQLiteでは、UPDATE SQLコマンドを使用してレコード内の値を変更できます。

UPDATEは、INSERT（列とその目的の値を指定する点で）およびDELETE（特定のレコードを対象とするために必要な条件を提供する点で）と類似した機能を持ちます。また、データを個別に、または一括で変更することもできます。この記事では、すでにテーブルに保存されているデータを効果的に管理するためにUPDATEをどのように使用するかについて詳しく説明します。

UPDATEを使用したデータの変更

UPDATEコマンドの基本的な構文は次のようになります。

UPDATE my_table
SET
        column1 = value1,
        column2 = value2,
WHERE
        id = 1;

基本的な構造は3つの異なる句で構成されます

• 操作対象のテーブルの指定
• 更新する列とその新しい値の提供
• SQLiteがどのレコードを一致させるかを判断するために評価する必要がある条件の定義

上記のように列に直接値を割り当てることもできますが、INSERTコマンドでよく見られるように、列リストの構文を使用することもできます。

例えば、上記の例を次のように変更できます。

UPDATE my_table
SET (column1, column2) =
        (value1, value2)
WHERE
        id = 1;

UPDATEコマンドによって変更されたレコードの返却

デフォルトでは、SQLiteはUPDATEステートメントによって影響を受けた行数を表示しません。しかし、SQLiteはバージョン3.35.0でPostgreSQLをモデルにしたRETURNING句を追加しました。この句により、コマンドは変更されたレコードのすべてまたは一部を返します。

アスタリスク*記号を使用すると、SELECTステートメントと非常によく似た方法で、変更された行のすべての列を返すことができます。

UPDATE my_table
SET
        column1 = value1,
        column2 = value2,
WHERE
        id = 1
RETURNING *;

さらに、ASを使用して、エイリアスを使用するかどうかにかかわらず、表示したい正確な列を指定することもできます。

UPDATE my_table
SET
        column1 = value1,
        column2 = value2
WHERE
        id = 1
RETURNING column1 AS 'first column';

別のテーブルの値に基づいてレコードを更新する

新しい外部データに基づいてデータを更新することは、比較的効率的なプロセスです。テーブル、列、新しい値、およびターゲットとなる条件を提供するだけで済みます。

しかし、SQLiteでは、データベース内の別のテーブルの情報に基づいて、UPDATEを使用してテーブルの値を条件付きで更新することもできます。基本的な構文は次のようになります。

UPDATE table1
SET table1.column1 =(
    SELECT table2.column1
    FROM table2
    WHERE table1.column2 = table2.column2);

ここでは、table1のcolumn1の値を、table2に対するSELECTサブクエリの戻り値に直接更新していますが、これはtable1のcolumn2がtable2のcolumn2と一致する行に限られます。FROM句は2つのテーブル間の接続を示し、WHEREは条件を指定します。

例として、bookとauthorという2つのテーブルがあるとしましょう。

CREATE TABLE author (
  id INTEGER PRIMARY KEY,
  first_name TEXT,
  last_name TEXT,
  last_publication TEXT
);

CREATE TABLE book (
    id INTEGER PRIMARY KEY,
    author_id INT REFERENCES author.id
    title TEXT,
    publication_year INTEGER
);

INSERT INTO author (first_name, last_name)
VALUES
        ('Leo', 'Tolstoy'),
        ('James', 'Joyce'),
        ('Jean-Paul', 'Sarte');

INSERT INTO book (author_id, title, publication_year)
VALUES
        (1, 'Anna Karenina', '1877'),
        (1, 'War and Peace', '1867'),
        (2, 'Ulysses', '1920'),
        (2, 'Dubliners', '1914'),
        (3, 'Nausea', '1938');

これら2つのテーブルは、book.author_idがauthor.idを参照する関係を持っています。現在、authorテーブルのlast_publicationはNULLです。FROM句とWHERE句を使用して両方のテーブルを結びつけ、bookテーブルにある著者の最新出版物でこれを埋めることができます。

ここで、last_publicationを更新する例を示します。

UPDATE author
SET last_publication=(
  SELECT title
  FROM book
  WHERE author_id = author.id
  ORDER BY author_id, publication_year DESC);

ここでauthorテーブルをクエリすると、データベース内の最新の出版物のタイトルが表示されます。

SELECT * FROM author;

+------------+------------+-----------+--------------------------+
      id       first_name  last_name      last_publication     
+-------------+------------+-----------+--------------------------+
       1       Leo          Tolstoy        Anna Karenina       
       2       James         Joyce             Ulysses         
       3       Jean-Paul     Sarte             Nausea          
+-------------+------------+-----------+--------------------------+

まとめ

このガイドでは、UPDATEコマンドを使用してテーブル内の既存データを変更する基本的な方法について説明しました。これらの基本的な概念を実行することで、テーブル内の既存の行を識別するために必要な正確な条件を指定し、列名を値で更新し、オプションでRETURNINGを使用して影響を受けた行を返すことができます。UPDATEコマンドは、データベースへの初回挿入後のデータ管理に不可欠です。

よくある質問

UPDATE my_table
SET
        column1 = value1,
        column2 = value2,
WHERE
        id = 1;

UPDATE my_table
SET
        column1 = value1,
        column2 = value2,
WHERE
        color = 'blue';

UPDATE my_table
SET
        column1 = value1,
        column2 = value2,
WHERE
        id = 1
RETURNING *;