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

はじめに

データがデータベースに入力されると、テーブル内で静的なままになる可能性は非常に低いです。データを最新の状態に保ち、関連性を維持するために、データはそれらが表すシステムの変化を反映するように更新されます。SQLiteでは、UPDATE SQLコマンドを使用してレコードの値を変更できます。

UPDATEは、INSERT（列とその目的の値を指定するという点で）およびDELETE（特定のレコードをターゲットにするために必要な基準を提供するという点で）と同様の機能を果たします。また、データを1つずつまたは一括で変更することもできます。この記事では、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句を使用して2つのテーブルを結合することにより、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コマンドは、データベースへの初期挿入後にデータを管理するために不可欠です。

FAQ

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